Set analysis help

[mahitham]

Hi,

Can any one please help me on below issue.

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 help me to show the second Max date values as 8,4 even the max report date is selected.

Thanks in advance

[Rodj]

I might be misunderstanding what you are wanting but if you make a selection against your date column you are limiting the dimension values that your set analysis will be applied to. If you want to get the max date ignoring your selections you need to use a different set. Currently all your expressions are for the set of {$}, which includes any selections you have made. If you use the set of all values ignoring selections {1} you might be heading in the right direction.

[mahitham]

Hi @Rodj 

Thanks for your reply.

By passing {1} user can't apply the selections.

But 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.

Please let me know Is there any way to modify the SecMaxDate expression to fix the issue.

[sunny_talwar]

Is this used a dimension or expression... if expression, then why do you need Aggr() function here? Try this

Count(DISTINCT{<[Data Sets] = {"=Len(Trim(Only({<[Report Date]>} [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])

 if dimension... try this

Aggr(
Count(DISTINCT{<[Data Sets] = {"=Len(Trim(Only({<[Report Date]>} [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])
, Project)