topic Re: Get Max date and Max value with multiple dimension in QlikView

Get Max date and Max value with multiple dimension

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

Hi Communities,

I tried to use FirstSortedValue function to find the max date and max value correspondingly of multiple dimensions.

However, there're missing value in the result.

I'm wondering if there's another function could be applied for multiple dimensions(4 dimensions)?

************ Original Expression **************

Max Date: = Max(Date)

Max Value: = FirstSortedValue(Value, -(Date*10000+Value))

************ Original Expression **************

Thanks in advanced.

Re: Get Max date and Max value with multiple dimension

olivierrobin — Tue, 10 Apr 2018 09:34:41 GMT

hello

if you are in a chart, maybe you could use it in combination with aggr() function

Re: Get Max date and Max value with multiple dimension

sunny_talwar — Tue, 10 Apr 2018 10:35:54 GMT

What exactly are you looking to get? Max Value on the Max Date?

Re: Get Max date and Max value with multiple dimension

katetsan — Wed, 11 Apr 2018 00:05:34 GMT

Hi Sunny,

Yes, I'm looking for max value on max date.

Re: Get Max date and Max value with multiple dimension

sunny_talwar — Wed, 11 Apr 2018 00:56:10 GMT

What if you add DISTINCT here

FirstSortedValue(DISTINCT Value, -(Date*10000+Value))

Re: Get Max date and Max value with multiple dimension

katetsan — Wed, 11 Apr 2018 01:01:54 GMT

Hi Sunny,

It works perfectly.

May I ask why should we add distinct in the expression?

Re: Get Max date and Max value with multiple dimension

sunny_talwar — Wed, 11 Apr 2018 01:03:26 GMT

It might be possible that the Max(Value) on Max(Date) might repeat more than once... if that is the case, FirstSortedValue() needs DISTINCT to show the value... it's just how the function behaves.