Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.