Show Value on Max Date

[Anonymous]

Hi,

I have a question regarding QlikView.

I have a data like this:

Product	Date	Price
A	1/18/2018	100
B	1/15/2018	200
B	1/16/2018	300

I want to create a pivot table that shown the latest date only for each product. For example for Product B there are two dates (1/15/2018 and 1/16/2018). I want to show maximum date of product B (1/16/2018)

So the result will be like this:

But I also want to show the price on 1/16/2018. Can you help me to find the way ?

I also attached my sample file.

Thanks in advance.

[devarasu07]

Hi,

Try below method,

Method 1:

Dimension:

Product

measure:

Max(aggr(sum(Price),Date))

Method 2:

Sum(Aggr(FirstSortedValue(Price, -Date), Product))   // best option

[justinphamvn · Accepted solution]

Hi Chou,

Try like this measure:

FirstSortedValue(aggr(Sum(Price),Date,Product),-Date)

Hope this helps.

Justin.

[Anonymous]

Hi Devarasu,

Thanks for your answer.

But I want to show the price on max(date) not max(price)

so if i change the data like this :

Product	Date	Price
A	1/18/2018	100
B	1/15/2018	300
B	1/16/2018	200

The price of product B must be 200 on 1/16/2018

Thanks

[devarasu07]

Hi,

this method should work for u. give a try

Sum(Aggr(FirstSortedValue(Price, -Date), Product))

Thanks

Deva

[Anonymous]

Hi,

Thank you very much. It solved my problem perfectly.