Max Date Per Value

[Anonymous]

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

[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 · Accepted solution]

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)

[Anonymous]

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]

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]

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