Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator 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
sunny_talwar

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
Anil_Babu_Samineni

Perhaps this? COUNT({<[Report Date]={'$(=Max(Date([Report Date],'DD/MM/YYYY'), 2))'}>}DISTINCT [Element Name])
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
DavidM
Partner - Creator II
Partner - Creator II

Max has optional Rank Variable. So like this

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

sunny_talwar

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

 

sunny_talwar

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])