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

[#1]

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 Name	Last Date Visited (in Last 3 Months)	Last Price
ABC	03/03/2013	2
DEF	22/01/2013	2
GHI	08/03/2013	3
JKL	27/02/2013	1
MNO	22/02/2013	3

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

[Gysbert_Wassenaar]

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.

[#3]

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.