Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Max Date Per Value

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,.

Capture.JPG

1 Solution

Accepted Solutions
sunny_talwar

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)

Capture.PNG

View solution in original post

14 Replies
Kushal_Chawda

try

FirstSortedValue(Value, -Date)

sunny_talwar

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?

sunny_talwar

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)

Capture.PNG

Anonymous
Not applicable
Author

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

ex Max(Date) -1 ?

sunny_talwar

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

Anonymous
Not applicable
Author

The second highest available date

i need like the pic i add from above

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

Add a set analysis like this:

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

PFA