Substract 2 different rows of a single table

[Anonymous]

Hi All,

I am developing an application in qlikview where I need a formula to substract 2 different rows of a single table

Here is the sample of the table I am having.

Date: Different dates of a perticular month (here:D1,D2,D3..etc)

Field 1: Field2's Value depends on this column

Field 2: Its a calculated field, derived from the values of Field 1.

The logic for Field 2 is first row will be 0 as it dont have no previous value. The 2nd row will be Value of 2nd Row of Field 1- Value of 1st row of Field 1

i.e: Current row value-previous row value.

Can anyone help with this? I would prefer to do the calculation in Script rather than putting a formula in a pivot table as the data is too huge and it can take a lot of time to calculate. But if its not possible in script I will go for formula.

Kindly help.

[sivarajs]

check this link http://community.qlik.com/message/240988#240988

[jonathandienst]

Hi

You need something like:

If(RowNo(TOTAL) = 1, 0, Field1 - Above(Field1))

Regards

Jonathan

[Anonymous]

Thank You Sivaraj & Jonathan,

This helped to resolve the issue by formula. But is it possible to do with script? Because Above() is not working in script.

Error in expression:
ABOVE is not a valid function

[#5 · Accepted solution]

You could try the peek function.

If(RowNo() = 1, 0, Field1 - peek(Field1,-1))

You have to make sure everything is sorted in the right way.

[#6]

Actually i also have the same issue of subtracting  two row of the same table.

I have to do it at the front end level.

And the If(RowNo() = 1, 0, Field1 - peek(Field1,-1)) is not working properly.

Can any body guide me on this?

[#7]

Thank you Frank its working now for me.

[Anonymous]

Hi Frank,

I am facing the same problem in my development, but i need to subtrct the two row from different columns in the same table.

Example:

        Time 1    Time 2

             3              2

             6              3

Tatal    9               5   =4  (9-5)

Can you please suggest how can i write expression for 9-5= 4 and  Multply 4 with some other number like 4*9=36.

Thanks

Bhupal

[anbu1984]

In Text box,

=Sum(Time1) - Sum(Time2)

In Chart,

=Sum(Total Time1) - Sum(Total Time2)

[linoyel]

Thank you Jonathan!!!

That's exactly what I was looking for!!!!!