topic Re: Get Previous Values in QlikView

Get Previous Values

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

Hi Everyone,

I have the sample data below and i would like to get the Previous Value Column,

For each Quarter, get the value of the previous one, either in the script or in the chart

Thank's in Advance

Re: Get Previous Values

vishsaggi — Wed, 23 May 2018 17:38:10 GMT

Try this in your Straight table expr

= IF(RowNo(TOTAL) = 1 OR RowNo(TOTAL) =2, 0, Above(TOTAL Sum(Value),2))

Re: Get Previous Values

eduardo_dimperio — Wed, 23 May 2018 18:31:26 GMT

SCRIPT:

LOAD

VALUE

PEEK(VALUES) AS PREVIOS VALUE

RESIDENT YOURTABLE

ORDER BY

ASC VALUE;

Re: Get Previous Values

zjalalacssi — Thu, 24 May 2018 07:22:15 GMT

Hello,

Thank you for your reply

This is the result i get when applying your formula.

Note please that in my final Table i won't need the Quarter anymore.

Thank you very much

Re: Get Previous Values

vishsaggi — Thu, 24 May 2018 14:09:52 GMT

Ok, is that not your expected output? if not what should be your expected output please let us know.

Re: Get Previous Values

zjalalacssi — Thu, 24 May 2018 14:18:23 GMT

Actually the Expected Result is:

Quarter	DIM1	DIM2	DIM3	PRIMES	Prev
Q1	D1	DD1	DD1	10
Q2	D1	DD1	DD2	20	10
Q3	D1	DD1	DD3	30	20
Q4	D1	DD1	DD4	60	30
Q1	D2	DD2	DD5	100
Q2	D2	DD2	DD6	200	100
Q3	D2	DD2	DD7	300	200
Q4	D2	DD2	DD8	400	300

Thank you

Re: Get Previous Values

Anil_Babu_Samineni — Fri, 25 May 2018 03:33:53 GMT

Can you do this?

Aggr(Above(Sum(PRIMES)), DIM1)

Re: Get Previous Values

Anonymous — Fri, 25 May 2018 04:58:41 GMT

try this on your Previous Field expression in straight table..

aggr(above( aggr(PRIMES,DIM1,Quarter)),DIM1,Quarter)

Re: Get Previous Values

jonathandienst — Fri, 25 May 2018 04:59:33 GMT

How about this:

LOAD Quarter,

DIM1,

DIM2,

DIM3,

PRIMES,

If(AutoNumberHash256(DIM1, DIM2, DIM3)

= Previous(AutoNumberHash256(DIM1, DIM2, DIM3)),

Previous(PRIMES)

) as Prev

FROM ....

ORDER BY DIM1, DIM2, DIM3, Quarter

Re: Get Previous Values

Anonymous — Fri, 25 May 2018 05:00:02 GMT

aggr(above( aggr(PRIMES,DIM1,Quarter)),DIM1,Quarter)

Re: Get Previous Values

Anonymous — Fri, 25 May 2018 05:30:17 GMT

Hi Zineb,

May following code is helpful,

Table:

LOAD * INLINE [

Quater, Dim1, Dim2, Dim3, Value

Q1, D1, DD1, D001, 10

Q1, D1, DD1, D002, 20

Q2, D1, DD1, D001, 33

Q2, D1, DD1, D002, 54

Q3, D1, DD1, D001, 90

Q3, D1, DD1, D002, 20

Q4, D1, DD1, D001, 129

Q4, D1, DD1, D002, 40

];

Load *,

if(IsNull(peek(Value,-2)),0,peek(Value,-2)) as Previous

Resident Table

;

In Front end just take table box,

just go to sort in properties and sort the table as follow

Re: Get Previous Values

zjalalacssi — Fri, 25 May 2018 07:55:59 GMT

Thank you everyone for your reponses, but all the solutions provided are not working.

I have tried this solution, to get Previous Data in a separate table and join it to my original table using a new generated key and playing with the quarter number.

PREVIOUS_DATA:

Load

'Q'&num(SubField(Quarter,'Q',2)+1) &'-'&DIM1&'-'&DIM2&'-'&DIM3 as Key_quarter,

Sum(PRIMES) as PrevPRIMES

resident MyTable

Group by Quarter,DIM1,DIM2,DIM3;

It worked perfectly.

Thank's Again.

Re: Get Previous Values

qlikviewwizard — Fri, 25 May 2018 09:46:14 GMT

Hi zjalalacssi,

Try like this.

Re: Get Previous Values

qlikviewwizard — Sun, 27 May 2018 01:33:54 GMT

Hi,

Did you try my solution also?

aggr(Above(Sum( PRIMES ) ),DIM1,DIM2,DIM3)