max of date help

[mahitham]

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

[Anil_Babu_Samineni]

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

[DavidM]

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]

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.

[mahitham]

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.

[sunny_talwar · Accepted solution]

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