6 Replies Latest reply on Nov 17, 2011 1:58 PM by KevinSalisbury

    Tracking Price Changes

    KevinSalisbury

      Title

      Tracking Price Changes

      Post

      Hello,

      I have a system that records invoices and I'm trying to find a way for me to produce a report that will track only price changes of purchased items that revolve around a product ID. Does anyone have any good suggestions? I appreciate the help!

        • 1. Re: Tracking Price Changes
          philmodjunk

          Make a summary report based on the line items table.

          Sort your records by Product ID, then by price. (you may want to include the sales date as well)

          Use a sub summary part, when sorted by price to display data about the product for that price. You can use a summary field that counts the ProductID field to produce a count of how many items where sold at that price.

          If you delete the body part of this layout, only the rows produced by the sub summary part or parts will be visible.

          • 2. Re: Tracking Price Changes
            KevinSalisbury

            Im testing this out now to see if I understand, but is there a way to calculate the exact price change between Product ID's in a sorted-by-date items list? For instance, I want to be able to see that I bought tomatoes today for $25 but bought the same ones last week for $10 less.

            • 3. Re: Tracking Price Changes
              philmodjunk

              Do you want the previous price for the same productID or the previous price for the same productID that is different from the current price?

              example:

              Price   Difference
              5            0
              5            0
              10          +5
              8            -2
              8             0
              8             0

              or this?

              Price   Difference
              5            0
              5            0
              10          +5
              8            -2
              8                -2
              8                -2

              • 4. Re: Tracking Price Changes
                KevinSalisbury

                the first.

                • 5. Re: Tracking Price Changes
                  philmodjunk

                  Ok, that's pretty easy to do using an unstored calclaution field that uses the GetNthRecord function.

                  This example would be used to list all the transactions, sorted by product ID, then by transaction date in ascending order, but does not use a sub summary layout part:

                  The calculation fieild would be used like this:

                  If (ProductID = GetNthRecord ( ProductID; Get (RecordNumber ) - 1 )
                      Price - GetNthRecord ( Price ; Get (RecordNumber ) - 1 ) ;
                       0
                      )

                  Note that this calculation only works when your foudn set contains the correct records sorted into a workable sort order.

                  • 6. Re: Tracking Price Changes
                    KevinSalisbury

                    Thant's awesome! I had a feeling the solution invovlved a calculation that I didn't fully understand. Thank you so much!