Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

2 Replies

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.


talk is cheap, supply exceeds demand
Not applicable

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.

Community Browser