Date help

[mahitham]

Hi,

Can any one please help me on below issue.

 I have the requirement like when User selects 21/11/2019 then secMaxDate column has to show 11/10/2019 count.

When he selects 11/10/2019 then next date in source 1/10/2019 count has to display in secMaxDate column.

I have used below expressions for

Max Date=aggr(count( distinct{<[Data Sets]={"=len(Trim([Data Sets]))>0"},
[Report Date] = {"$(=Date(Max([Report Date]), 'DD/MM/YYYY'))"},
Plan= {'$(=$(vMaxYear) &' '&'Plan')'}>}[Data Sets]),[Projects])

Second Max Date=aggr(count( distinct{<[Data Sets]={"=len(Trim([Data Sets]))>0"},
[Report Date] = {"$(=Date(Max({<[Report Date] = {[<=$(=Date(Max([Report Date]), 'DD/MM/YYYY'))]}>} [Report Date], 2), 'DD/MM/YYYY'))"},
Plan= {'$(=$(vMaxYear) &' '&'Plan')'}>}[Data Sets]),[Projects])

Here Max Date expression is working fine. But in Second Max Date without selecting any report date getting correct values but when I have selected Report date values are changing. But we have to use max date on Default selector.

Please let me know am I missed anything in the SecMaxDate Column.

Thanks in advance

[tresesco]

Could you share your sample app and explain the expected output?

[sunny_talwar]

Check here

https://community.qlik.com/t5/New-to-Qlik-Sense/Set-analysis-help/m-p/1652071/highlight/false#M15225... 

[Brett_Bleess]

Best I have in addition to existing posts are a couple of Design Blog links that may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Expression-Search/ba-p/1463907

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Here is the base Design Blog URL in case you want to further search that area for additional posts:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett