Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
mahitham
Contributor II

max of date help

Hi,

Can any one please help me on below requirement.

I have dates in [Report Date] field like below

Report Date

11/09/2019

21/09/2019

30/09/2019

I have to compare first Max date and Second Max date results 

First Max Date:

COUNT({<[Report Date]={'$(=Max(Date([Report Date],'DD/MM/YYYY')))'}>}DISTINCT [Element Name])

Please help me to write the expression for Second Max date i.e., 21/09/2019 

Thanks in advance

1 Solution

Accepted Solutions

Re: max of date help

Try this

Count({<[Report Date] = {"$(=Date(Max({<[Report Date] = {[<=$(=Date(Max([Report Date]), 'DD/MM/YYYY'))]}>} [Report Date], 2), 'DD/MM/YYYY'))"}>} DISTINCT [Element Name])

View solution in original post

6 Replies

Re: max of date help

Perhaps this? COUNT({<[Report Date]={'$(=Max(Date([Report Date],'DD/MM/YYYY'), 2))'}>}DISTINCT [Element Name])
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Partner
Partner

Re: max of date help

Max has optional Rank Variable. So like this

COUNT({<[Report Date]={'$(=Max(Date([Report Date],'DD/MM/YYYY'),2))'}>}DISTINCT [Element Name])

Re: max of date help

I would put Max before Date in the set modifier even for max date

Count({<[Report Date] = {"$(=Date(Max([Report Date]), 'DD/MM/YYYY'))"}>} DISTINCT [Element Name])

and then the second max date count would be

Count({<[Report Date] = {"$(=Date(Max([Report Date], 2), 'DD/MM/YYYY'))"}>} DISTINCT [Element Name])
mahitham
Contributor II

Re: max of date help

Hi @sunny_talwar 

Thanks for your reply.

The expression is working fine when I haven't selected any date. But when I selected 21/10/2019 then the previous date 11/10/2019 need to get. Same like when there are multiple dates from the selected date to previous date has to be visible.

Here the issue is when I have selected 21/10/2019  its showing blank without any date select its working.

Could you please help me to get the previous date from the selected date.

 

DATE.png

Previous.png

 

mahitham
Contributor II

Re: max of date help

Hi @sunny_talwar 

Thanks for your reply.

The expression is working fine when I haven't selected any date. But when I selected 21/10/2019 then the previous date 11/10/2019 need to get. Same like when there are multiple dates from the selected date to previous date has to be visible.

Here the issue is when I have selected 21/10/2019  its showing blank without any date select its working.

Could you please help me to get the previous date from the selected date.

 

DATE.png

Previous.png

 

Re: max of date help

Try this

Count({<[Report Date] = {"$(=Date(Max({<[Report Date] = {[<=$(=Date(Max([Report Date]), 'DD/MM/YYYY'))]}>} [Report Date], 2), 'DD/MM/YYYY'))"}>} DISTINCT [Element Name])

View solution in original post