Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

DateShopProductPrice
29/11/2012GHIXYZ4
03/12/2012JKLXYZ3
08/12/2012GHIXYZ2
12/12/2012DEFXYZ1
17/12/2012ABCXYZ3
21/12/2012DEFXYZ3
26/12/2012MNOXYZ4
30/12/2012ABCXYZ1
04/01/2013DEFXYZ2
08/01/2013DEFXYZ3
13/01/2013JKLXYZ4
17/01/2013ABCXYZ2
22/01/2013DEFXYZ2
26/01/2013GHIXYZ4
31/01/2013MNOXYZ3
04/02/2013GHIXYZ3
09/02/2013MNOXYZ4
13/02/2013MNOXYZ2
18/02/2013ABCXYZ2
22/02/2013MNOXYZ3
27/02/2013JKLXYZ1
03/03/2013ABCXYZ2
08/03/2013GHIXYZ3

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

1 Reply
Not applicable
Author

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.