How Create Prior Year Column

[ksomosera10]

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!

[Michiel_QV_Fan]

check out the above() function.

[gowtham_patnaik]

try above() function

[ksomosera10]

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

[ksomosera10]

Please check out my reply on Michiel van de Goor

[ksomosera10]

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

[gowtham_patnaik]

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.

[ksomosera10]

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

[gowtham_patnaik]

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)

[ksomosera10]

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.