2 Replies Latest reply: Apr 15, 2013 7:35 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