topic Re: Show Value on Max Date in QlikView

Show Value on Max Date

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Show Value on Max Date

devarasu07 — Thu, 18 Jan 2018 07:47:05 GMT

Hi,

Try below method,

Method 1:

Dimension:

Product

measure:

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

Method 2:

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

Re: Show Value on Max Date

justinphamvn — Thu, 18 Jan 2018 07:50:54 GMT

Hi Chou,

Try like this measure:

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

Hope this helps.

Justin.

Re: Show Value on Max Date

Anonymous — Thu, 18 Jan 2018 07:52:45 GMT

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

Re: Show Value on Max Date

devarasu07 — Thu, 18 Jan 2018 07:56:41 GMT

Hi,

this method should work for u. give a try

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

Thanks

Deva

Re: Show Value on Max Date

Anonymous — Thu, 18 Jan 2018 08:31:14 GMT

Hi,

Thank you very much. It solved my problem perfectly.