topic Re: Date help in QlikView

Date help

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

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

Re: Date help

tresB — Thu, 28 Nov 2019 06:38:53 GMT

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

Re: Date help

sunny_talwar — Wed, 04 Dec 2019 19:24:25 GMT

Check here

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

Re: Date help

Brett_Bleess — Tue, 10 Dec 2019 18:37:18 GMT

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