topic Re: Getting max(year)-1 in set analysis in QlikView

Getting max(year)-1 in set analysis

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

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!

Re: Getting max(year)-1 in set analysis

tresB — Thu, 07 Nov 2019 07:11:05 GMT

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

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

Re: Getting max(year)-1 in set analysis

vicky_qv — Thu, 07 Nov 2019 07:54:41 GMT

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?

Re: Getting max(year)-1 in set analysis

tresB — Thu, 07 Nov 2019 07:59:16 GMT

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

Re: Getting max(year)-1 in set analysis

vicky_qv — Thu, 07 Nov 2019 08:12:03 GMT

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.

Re: Getting max(year)-1 in set analysis

Arthur_Fong — Thu, 07 Nov 2019 08:59:06 GMT

Try this:

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

Re: Getting max(year)-1 in set analysis

tresB — Thu, 07 Nov 2019 09:02:43 GMT

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