topic Re: Calculating metric for prior year in QlikView

Calculating metric for prior year

AB108 — Tue, 23 Feb 2021 00:38:45 GMT

Hi,

I am trying to calculate sum of a metric for the previous year in pivot table.

The year is a dimension(listbox) which is selected by user-FYear

Tried a couple of combinations like below formula but not getting the results:

sum({<[FYear]={$(=Max([FYear])-1)>}[Qty]) ---gives nothing

Sum({<[FYear]={$(=AddYear([FYear],-1))}>}[Qty] ) ---gives zero

srishsum2017 — Tue, 23 Feb 2021 06:39:37 GMT

Try this, if its not work so please share some sample data.

sum({<[FYear]={"$(=Max([FYear])-1)"}>}[Qty])

MayilVahanan — Tue, 23 Feb 2021 07:20:06 GMT

I think, your FYear looks like below

in that case, you can use Dual() function and assign numeric values for FYear and then use max() function in set analysis, hope it helps.

AB108 — Tue, 23 Feb 2021 17:38:40 GMT

HI Sumit, This totally worked. Thanks a lot.

AB108 — Tue, 23 Feb 2021 17:41:00 GMT

Hi Mayil,

Fyear has straight up year values-2017,2018...

Corrected the expression, its working now.