topic Re: How Create Prior Year Column in QlikView

How Create Prior Year Column

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

Hi,

Im new at Qlik Sense and I need to create a column expression to create a Prior Year column.

You can check on my example on what I needed to accomplish.

Year || Sales || PriorYearSales

2013 || 420 || 0

2014 || 250 || 420

2015 || 143 || 250

2016 || 156 || 143

2017 || 320 || 320

I can create expressions to sum these values but I can't put the values to a different or prior year.

I hope someone could help me out here. Thanks!

Re: How Create Prior Year Column

Michiel_QV_Fan — Thu, 09 Feb 2017 11:41:25 GMT

check out the above() function.

Re: How Create Prior Year Column

gowtham_patnaik — Thu, 09 Feb 2017 12:09:24 GMT

try above() function

Re: How Create Prior Year Column

ksomosera10 — Fri, 10 Feb 2017 00:34:42 GMT

Thanks but I already tried above() it only copies the above rows. I need a flexible expression. Its a pivot table and year has a sub row of months. like this. I really need to know how its done. Its a big challenge for me. Thanks!

+2013 || 420 || 0

-2014 || 250 || 420

+ JAN || 100 || 220

+ FEB || 150 || 200

+2015 || 143 || 250

+2016 || 156 || 143

+2017 || 320 || 320

Re: How Create Prior Year Column

ksomosera10 — Fri, 10 Feb 2017 00:35:27 GMT

Please check out my reply on Michiel van de Goor

Re: How Create Prior Year Column

ksomosera10 — Fri, 10 Feb 2017 00:54:43 GMT

If I use above() function, how can I use it on drill-down rows. thanks!

Re: How Create Prior Year Column

gowtham_patnaik — Mon, 13 Feb 2017 05:43:36 GMT

Make use of functions like rangesum() and aggr()

try expressions like:

=Aggr(Rangesum(Above(sum({$< Year = {"vyear"}>} sales) 0, 1)),year, month)

Note:

Currently i am not having qlikview to check the expression.

Re: How Create Prior Year Column

ksomosera10 — Mon, 13 Feb 2017 06:49:41 GMT

Can I ask what is the "vyear"? is it the prior year or the exact year like "2017" Thanks!

Re: How Create Prior Year Column

gowtham_patnaik — Mon, 13 Feb 2017 07:15:30 GMT

vYear is just a variable declared,so that it will be applicable to particular year.

ignore that variable.

try this:

=Aggr(Rangesum(Above(sum( sales) 0, 1)),year, month)

Re: How Create Prior Year Column

ksomosera10 — Mon, 13 Feb 2017 07:29:31 GMT

Your response is very appreciated, but the expression didn't do anything after I've used your expression.

Did I did Something wrong? here is my expression from you:

=Aggr(Rangesum(Above(sum([Actual]) 0, 1)),year, month)

THANK YOU! hope you can help me figure this out.

Re: How Create Prior Year Column

gowtham_patnaik — Mon, 13 Feb 2017 08:32:23 GMT

can u upload some sample qvw. so that i can figure it out.