topic Re: Current Year Vs Prior Year Set Analysis not working in QlikView

Current Year Vs Prior Year Set Analysis not working

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

Hi Guys,

Current Year Vs Prior Year Set Analysis not working, it works if I hard code a year in or replicates the current year in the prior year set analysis.

Prior Year set analysis:

If(BusinessMetricName=1,num(sum({$<MC_Year={'=$(=GetFieldSelections(MC_Year)-1)'}>} [GWP Orig]),'#,##0'))

Current Year set analysis:

if(BusinessMetricName=1,num(sum([GWP Orig]),'#,##0'), ---------- gets Year from MC_Year selection.

Works perfect if hardcoded:

If(BusinessMetricName=1,num(sum({$<MC_Year={2016}>} [GWP Orig]),'#,##0'))

Thanks for the help that I know will come.

Re: Current Year Vs Prior Year Set Analysis not working

sunny_talwar — Tue, 22 Aug 2017 12:25:29 GMT

May be try this:

If(Only({1} BusinessMetricName) = 1,

Num(Sum({$<MC_Year = {'=$(=GetFieldSelections(MC_Year)-1)'}>} [GWP Orig]),'#,##0'))

Re: Current Year Vs Prior Year Set Analysis not working

sunny_talwar — Tue, 22 Aug 2017 12:26:26 GMT

Also, suggest using Max() instead of getfieldselections....

If(Only({1} BusinessMetricName) = 1, Num(Sum({$<MC_Year = {$(=Max(MC_Year)-1)}>} [GWP Orig]),'#,##0'))

Re: Current Year Vs Prior Year Set Analysis not working

Anonymous — Tue, 22 Aug 2017 13:02:31 GMT

You're a star, what was I doing wrong?

Re: Current Year Vs Prior Year Set Analysis not working

sunny_talwar — Tue, 22 Aug 2017 13:04:24 GMT

BusinessMetricName did not have any set analysis so as soon as you selected 2017, the 2016 BusinessMetricName became null.... I just added Only({1} ...) around BusinessMetricName to ignore selections