19 Replies Latest reply: Oct 24, 2012 6:14 AM by Christophe Brault

# Getting the previous row and calculate.

Hello and Good Day, I'm currently new in QlikView and I say this is one powerful B.I Tool.

I'm still learning and I need to solve something..

I have a table

DIDValueValue1
D112000 0
D213000 1000
D322000 0
D421500 -500
D52500-1000
D631000..

I need to compute for Value1 depending on the ID.

The computation is d2-d1, d4-d3, d5-d4

I find it hard to explain, but I just need to compute for Value1 depending on the next Value.

Thank you and Good Day.

P.S This is for scripting Thank you.

• ###### Re: Getting the previous row and calculate.

Hi,

I d'ont really understand your need.

How are your data when you load it, and what do you expect to do in the script exactly ?

• ###### Re: Getting the previous row and calculate.

it is from excel file. i loaded it.. and it has a lot of sheets.. so there is a for loop inside..

I have a field I need to compute. As I have said above, the results should be like this:

ID          Value     Value1

1           1000          0

1            2000      1000

1             500          -1500

2            100          0

2             200         100

• ###### Re: Getting the previous row and calculate.

Hi,

Try this  expression  in Value

above([Value])-[Value]

use this expression ,u get result

• ###### Re: Getting the previous row and calculate.

Hi, I will try this in the expression, but I prefer it in the script

• ###### Re: Getting the previous row and calculate.

You can use this :

If(Peek(ID)=ID,

Value-Peek(Value),0) as Value1,

Make sure your data are ordered by ID.

• ###### Re: Getting the previous row and calculate.

I will try this one, can you give me an example if possible? thank you

• ###### Re: Getting the previous row and calculate.

I tried your computation, yes it is working but the first value should be the one with the '-'

• ###### Re: Getting the previous row and calculate.

bump.

• ###### Re: Getting the previous row and calculate.

bump

• ###### Re: Getting the previous row and calculate.

my problem is like this http://community.qlik.com/message/240999

but i need to compute base on the ID

• ###### Re: Getting the previous row and calculate.

I tried your computation, yes it is working but the first value should be the one with the '-'

I don't understand this requirement. Can you explain more or share data ?

• ###### Re: Getting the previous row and calculate.

It seems that my data peeks in other rows, i don't know why it works like that.

attached file is what i'm testing..

• ###### Re: Getting the previous row and calculate.

Hi,

use Previous() to access the last row.

D,

ID,

Value,

Value - Previous( Value ) as Value1

FROM

Source

ORDER BY

D;

• ###### Re: Getting the previous row and calculate.

Hi,

There are several functions in qlikview to get the values from the previously loaded rows.

Try out Previous and peek functions. you can find beautiful descriptions from QlikView Help Menu or Press F1.

Hope this helps.

Regards

Andrew Hudson

• ###### Re: Getting the previous row and calculate.

thank you for replies

alhough i'm still having problem regarding my data.

I think the problem is because there is a date field. I have attached my .qvw, you can check it out.

Thanks

• ###### Re: Getting the previous row and calculate.

bump.

• ###### Re: Getting the previous row and calculate.

Hello,

from

`if([Plate Number] = Peek([Plate Number]), [Odometer Reading] - Peek([Odometer Reading],0)) as peek`

why `Peek([Odometer Reading],0))?`

Do you really want substract the first value from the table in every row?

```left join(A)
Date,
Time,
if([Plate Number] = Previous([Plate Number]), [Odometer Reading] - Previous([Odometer Reading])) as new_peek
Resident
A
Order by Date, Time;
```

I have an expample enclosed.

• ###### Re: Getting the previous row and calculate.

Wow thank you very much!

I would try to apply it now to my work. Thanks