Conditional if to pick correct expression

[vsap2000]

Hi Guys,

For an Inquiry Total KPI, I have written an expression that first it checks for current fiscal year(Apr 2021) if it is true it will calculate total inquiries for last fiscal year (Apr 2020 thru Mar 2021) else it will calculate total for current fiscal year after Apr 2021.

In short, it is Apr 2021 then calculate totals from Apr 20 thru Mar 21 and if it is other than Apr 2021, if May 2021 then calculate  total only for Apr 2021, if it is Jun 2021 it will calculate for Apr 2021 thru May 2021 and so on.

If(Max([Inq_MonthYear]) = Min({<IsCurrentYear = {'1'}>} [Inq_MonthYear]),
Count({<[InqDate]= {">=$(=MonthStart(ReloadTime(),-12))<$(=MonthStart(ReloadTime(),0))"}
>}Distinct InqKey) ,
Count({<[InqDate]= {"<$(=MonthStart(ReloadTime(),0))"},IsCurrentYear = {'1'}>}Distinct InqKey)

 )

I have filter for Fiscal Year 2020,2021,2022 and default is 2022.

I am facing the challenge this expression doesn't work when fiscal year = 2022, but when I remove the filter it gives me correct total, can anyone please tell me why it is not working.

If there is another approach I am open to that too.

Thanks in advance.

-V

[Rodj]

Hi V,

I'm possibly misunderstanding the issue but it sounds like it is working as expected. If you have FY 2022 selected then given the logic you are using I wouldn't expect to have any data as a result. In your various set expressions you've implied that currently selections be taken into account by not defining the set any other way, i.e. a set of {} is the same as a set of {$}, which equals current selections. If you wan to ignore your current selections you need to use the set {1}.

Cheers,

Rod

[vsap2000]

Hi Rod,

Thanks for reply, issue is when I don't select any filter it works very well but when I select Fy 2022, output is zero, I can't use {1} as there are other filters too for use. When you said "If you have FY 2022 selected then given the logic you are using I wouldn't expect to have any data as a result."  what it means? Can you please clarify ? Any other option can I use please let me know.

Thanks,

-v