Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
vicky_qv
Contributor III
Contributor III

Getting max(year)-1 in set analysis

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!

1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Try this:

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

View solution in original post

6 Replies
tresesco
MVP
MVP

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

 

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

vicky_qv
Contributor III
Contributor III
Author

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
MVP
MVP

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
Contributor III
Contributor III
Author

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
Partner - Specialist III
Partner - Specialist III

Try this:

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

tresesco
MVP
MVP

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