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

See attached example. Note, if there are several records with the same maximum date for the same shop then it's impossible to determine which price should be shown. That's why in those cases the firstsortedvalue function cannot return a value.

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

So, after forgetting about this issue over the weekend and relooking at it this morning, I realised I already have a unique key field in the fact table. Instead of getting MAX(Date), I can use MAX(Key) value. This will overcome the NULL result from FirstSortedValue.

I have attached the changed qvw using a unique key in the sample data originally supplied above.