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
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.