topic Re: Max Date Per Value in QlikView

Max Date Per Value

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

Hey Community,

i need to show Index_id and his value on max Date and the change between last date value

Thanks in advance for you help,.

Re: Max Date Per Value

Kushal_Chawda — Mon, 16 May 2016 08:13:52 GMT

try

FirstSortedValue(Value, -Date)

Re: Max Date Per Value

sunny_talwar — Mon, 16 May 2016 09:50:25 GMT

For ID 40, you have two values, how would you want to handle this? Avg the two values available? Sum or pick one of them randomly?

Re: Max Date Per Value

sunny_talwar — Mon, 16 May 2016 09:59:57 GMT

If you want to pick the average, you can try this:

FirstSortedValue(Aggr(Avg(VALUE), DATE, INDEX_ID), -Aggr(DATE, INDEX_ID, DATE))

and for change from last period, you can try this:

FirstSortedValue(Aggr(Avg(VALUE), DATE, INDEX_ID), -Aggr(DATE, INDEX_ID, DATE)) - FirstSortedValue(Aggr(Avg(VALUE), DATE, INDEX_ID), -Aggr(DATE, INDEX_ID, DATE), 2)

Re: Max Date Per Value

Anonymous — Mon, 16 May 2016 10:21:49 GMT

Thank you but how can i see the change from the last date

ex Max(Date) -1 ?

Re: Max Date Per Value

sunny_talwar — Mon, 16 May 2016 10:25:46 GMT

Max(Date) - 1 or the second highest available date?

Re: Max Date Per Value

Anonymous — Mon, 16 May 2016 10:26:40 GMT

The second highest available date

i need like the pic i add from above

Re: Max Date Per Value

sunny_talwar — Mon, 16 May 2016 10:28:12 GMT

I did provide an expression for second highest date. Here is it:

FirstSortedValue(Aggr(Avg(VALUE), DATE, INDEX_ID), -Aggr(DATE, INDEX_ID, DATE), 2)

UPDATE: The 2 at the end is telling it to pick the average value from 2nd highest date

Re: Max Date Per Value

Anonymous — Mon, 16 May 2016 10:58:04 GMT

If i want that it will show me only for specific Index_id Ex Only index_id 1,2,3,4 ?

Re: Max Date Per Value

sunny_talwar — Mon, 16 May 2016 11:00:14 GMT

Add a set analysis like this:

{<INDEX_ID = {1, 2, 3, 4}>}

PFA

Re: Max Date Per Value

Anonymous — Mon, 16 May 2016 11:01:37 GMT

Again Thank you so much for you amazing help thx !!!

Re: Max Date Per Value

sunny_talwar — Mon, 16 May 2016 11:03:37 GMT

No problem at all

Re: Max Date Per Value

Anonymous — Mon, 16 May 2016 11:22:41 GMT

One more thing can i create this kind of look whit the sign

if <-0.5 Red -

If >0.5 Green +

Re: Max Date Per Value

sunny_talwar — Mon, 16 May 2016 11:24:44 GMT

When you say sign do you mean the arrows? I think they cannot be in one cell, but if you can have two cells then this can work. Also for the colors you can use a background expression.

Re: Max Date Per Value

Anonymous — Mon, 16 May 2016 11:25:46 GMT

Yes this is what i was think also cool !