1 Reply Latest reply: Apr 25, 2013 9:28 AM by Rico van Zyl RSS

    Retrieving that Last Price within the Last 3 Months in Straight Table

      Hi All,

       

      I am working on a Pricing analysis and struggle to get the last price of a specific product at a specifc Shop within the last 3 months:

       

      Result I am looking for:

      Shop NameLast Date Visited (in Last 3 Months)Last Price
      ABC03/03/20132
      DEF22/01/20132
      GHI08/03/20133
      JKL27/02/20131
      MNO22/02/20133

       

      Transaction table:

      DateShopProductPrice
      29/11/2012GHIXYZ4
      03/12/2012JKLXYZ3
      08/12/2012GHIXYZ2
      12/12/2012DEFXYZ1
      17/12/2012ABCXYZ3
      21/12/2012DEFXYZ3
      26/12/2012MNOXYZ4
      30/12/2012ABCXYZ1
      04/01/2013DEFXYZ2
      08/01/2013DEFXYZ3
      13/01/2013JKLXYZ4
      17/01/2013ABCXYZ2
      22/01/2013DEFXYZ2
      26/01/2013GHIXYZ4
      31/01/2013MNOXYZ3
      04/02/2013GHIXYZ3
      09/02/2013MNOXYZ4
      13/02/2013MNOXYZ2
      18/02/2013ABCXYZ2
      22/02/2013MNOXYZ3
      27/02/2013JKLXYZ1
      03/03/2013ABCXYZ2
      08/03/2013GHIXYZ3

       

      I cannot get my expression to give the desired results as above in 1st table when selection Product XYZ from the selections.

      I have tried the following:

       

      1.     Sum(aggr(firstsortedvalue(Price,-TheDate),Product.ID)) -----> Returns 0, BUT... when I select a specific Shop, I get an answer.

      2.     =MAX({< 

                     ActiveDate = {"$(=MAX({$<  ActiveDate = {">=$(v3MStart)<=$(vLatestDate)"}, >} ActiveDate))"},

                          >} ActualPrice) ----> Yes, nested Set Analysis... Only shows a price in some instances, where there is only 1 price for the date. When I select a shop, I get the last price.

       

      I want the Last Price do display, without selecting a specific Shop.

       

      Any help will be greatly appreciated.

       

      Thanks,

       

      Rico

        • Re: Retrieving that Last Price within the Last 3 Months in Straight Table

          Ok, so here's what I did... not sure if this is "best practice", but I get the correct results...

          I create a unique key for each record in the fact table.

          I then use an IF to determine if there are multiple Prices sorted Last. If so, I use the MAX... expression to return the price, otherwise, last sorted price by key. See below

           

          IF(ISNULL(FirstSortedValue(Price, -NewKey)),

                         MAX({$< Date = {">=$(v3MStart)<=$(vLatestDate)"}, NewKey = {"=MAX(NewKey)"} >} Price),

                         FirstSortedValue(Price, -NewKey))

           

          In theory I though that the MAX... expression would be sufficient, but there is some snag and it doesn't return the correct price every time. By doing the combination as above, I get the correct results.

           

          If someone has a better suggestion, feel free to let me know.