Getting max(year)-1 in set analysis

[vicky_qv]

When I am using sum({<FiscalYear={"$(=max(FiscalYear)-1)"}>}Qty) and when no selection in FiscalYear field, My straight table is getting populated, but when I select any value in FiscalYear, I am getting nothing in table.

I wanted the above expression to work even when selection in the fiscalyear. e.g, if I select 2018, then 2017 should be assigned, for 2017, 2016 should be assigned.

Thanks!

[tresesco]

You would probably need selection context remover set in max() as well, like:

sum({<FiscalYear={"$(=max( {1} FiscalYear)-1)"}>}Qty) 

[vicky_qv]

Hi - Currently it is displaying 2019 because of fiscal year -1. But when I select 2020, nothing shows in the table and when I select 2019, it's showing 2019 data not 2018(fiscal year -1). 

And I have AGGR in expression like:

sum({<FiscalYear={"$(=max( FiscalYear)-1)"}>}aggr(sum(Qty),%Key)).

And if I use sum({<FiscalYear={"$(=max( {1} FiscalYear)-1)"}>}aggr(sum(Qty),%Key)), does this not ignore my selection in FiscalYear field?

[tresesco]

No it doesn't, because you have aggr(), sum() that don't have set analysis to ignore selection.

Try like:

 sum({<FiscalYear={"$(=max( {1} FiscalYear)-1)"}>}aggr( {<FiscalYear>} sum({<FiscalYear>} Qty),%Key))

[vicky_qv]

I do not want to ignore my selection. If I select 2018 , 2017 data should be shown in the table. Max(fiscalyear) should be determined based on my selection.

[Arthur_Fong · Accepted solution]

Try this:

sum({<FiscalYear={"$(=max( FiscalYear)-1)"}>}aggr(sum({<FiscalYear={"$(=max( FiscalYear)-1)"}Qty),%Key)).

[tresesco]

Could you share a sample app and explain expected put there?