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

# 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:

 Date Shop Product Price 29/11/2012 GHI XYZ 4 03/12/2012 JKL XYZ 3 08/12/2012 GHI XYZ 2 12/12/2012 DEF XYZ 1 17/12/2012 ABC XYZ 3 21/12/2012 DEF XYZ 3 26/12/2012 MNO XYZ 4 30/12/2012 ABC XYZ 1 04/01/2013 DEF XYZ 2 08/01/2013 DEF XYZ 3 13/01/2013 JKL XYZ 4 17/01/2013 ABC XYZ 2 22/01/2013 DEF XYZ 2 26/01/2013 GHI XYZ 4 31/01/2013 MNO XYZ 3 04/02/2013 GHI XYZ 3 09/02/2013 MNO XYZ 4 13/02/2013 MNO XYZ 2 18/02/2013 ABC XYZ 2 22/02/2013 MNO XYZ 3 27/02/2013 JKL XYZ 1 03/03/2013 ABC XYZ 2 08/03/2013 GHI XYZ 3

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.