topic Re: Find Max Date in QlikView

Find Max Date

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

Hi Techies,

I need to find max date where all dimensions are possible.

For Example:

I have A, B, C, D as Dimension

Max date of A, B, C is 9/7/2016

Max date of D is 9/8/2016

So here 9/7/2016 is the max date where all dimensions are possible.

How to get this in expression. No backend scripting. PFA.

Re: Find Max Date

Anil_Babu_Samineni — Fri, 09 Sep 2016 11:03:12 GMT

I didn't find your E dimension value?

Re: Find Max Date

effinty2112 — Fri, 09 Sep 2016 11:18:06 GMT

Hi Rangaraju,

Try:

=Max(Aggr(if(Concat(Distinct Dimension) = 'ABCD',Date),Date))

Cheers

Andrew

Re: Find Max Date

Fri, 09 Sep 2016 11:21:22 GMT

You forgot a convert number to a date.

Correct formula is:

=Date(Max(Aggr(if(Concat(Distinct Dimension) = 'ABCD',Date),Date)))

BR,

Bartek

Re: Find Max Date

rajuamet — Fri, 09 Sep 2016 11:22:03 GMT

Hi Anil Babu,

Updated my content again, thanks.

Re: Find Max Date

sunny_talwar — Fri, 09 Sep 2016 11:38:00 GMT

Set analysis alternative

=Date(Max({<Date = {"=Concat(DISTINCT Dimension) = 'ABCD' "}>}Date))

Re: Find Max Date

rajuamet — Fri, 09 Sep 2016 15:09:46 GMT

Awesome Sunny,

So it is like , the set analysis considers only the value in the Date field which is true for the condition "=concat(DISTINCT Dimension)='ABCD'" and fetches the max value from that. Nice

Re: Find Max Date

sunny_talwar — Fri, 09 Sep 2016 15:11:53 GMT

Absolutely. The search string is evaluated as a true or falls for each Dimension (Date in your case) and the max will be found from those rows which meets the set analysis criteria.