Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
check this link http://community.qlik.com/message/240988#240988
Hi
You need something like:
If(RowNo(TOTAL) = 1, 0, Field1 - Above(Field1))
Regards
Jonathan
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
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.
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?
Thank you Frank its working now for me.
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
In Text box,
=Sum(Time1) - Sum(Time2)
In Chart,
=Sum(Total Time1) - Sum(Total Time2)
Thank you Jonathan!!!
That's exactly what I was looking for!!!!!