topic Re: Add Value from Last Year in Table in QlikView

Add Value from Last Year in Table

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

Hello,

I have the following table:

- Period: YYYYMM

Period	Value
201501	10
201502	15
201503	5
201504	20
201505	25
201506	30
201507	100
201508	150
201509	135
201510	500
201511	200
201512	235
201601	5
201602	15
201603	20
201604	50
201605	45
201606	95
201607	70
201608	750
201609	200
201610	250
201611	380
201612	355

What I need is to create a table like this:

Period	Value	Previous_Value
201601	5	10
201602	15	15
201603	20	5
201604	50	20
201605	45	25
201606	95	30
201607	70	100
201608	750	150
201609	200	135
201610	250	500
201611	380	200
201612	355	235

This table contains last 12 months with the corresponding VALUE and the PREVIOUS_VALUE from last year.

Do you know how could I do this, directly on my Straight Table as an expression?

Because I've tried doing something like: =Above(Sum({<Period>} Value), 12) * Avg(1)

And it shows me the following which is ok if I don't touch anything:

But when I sort the table by Period, I don't see it correctly:

Or if I make a selection for a Year, I can't see the last year value (or if I select some periods):

I'd need to create this expression on QlikView (and also Qlik Sense), without breaking the object.

Is there any solution for this? I've been searching since a long time and didn't find it so far.

Thank you!

Re: Add Value from Last Year in Table

Anil_Babu_Samineni — Tue, 09 May 2017 04:49:35 GMT

Don't worry about that?

Use this expression for last year

=Above(Sum({1<Period>} Value), 12) * Avg(1)

Re: Add Value from Last Year in Table

Digvijay_Singh — Tue, 09 May 2017 05:58:39 GMT

I don't have license so cannot open your QVW, if you write your expression like below, I think it will do sorting properly -

Exp 1 - Aggr(Sum({1<PeriodSeq={">=$(=Max({1}PeriodSeq)-11)"}>}Value),PeriodSeq)

Exp 2 - Aggr(Above(Sum({1} Value), 12) ,PeriodSeq)

I have added new field PeriodSeq so that comparison can be performed easily, added in the script as -

AutoNumber(Period) as PeriodSeq

Check below the list in descending order showing properly, also it won't be affected by any select as we have used {1} in aggregation function to disregard the selections.