topic Re: Substract 2 different rows of a single table in QlikView

Substract 2 different rows of a single table

Anonymous — Tue, 24 Jul 2012 12:14:14 GMT

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.

Re: Substract 2 different rows of a single table

sivarajs — Tue, 24 Jul 2012 12:25:34 GMT

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

Re: Substract 2 different rows of a single table

jonathandienst — Tue, 24 Jul 2012 12:29:01 GMT

You need something like:

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

Regards

Jonathan

Re: Substract 2 different rows of a single table

Anonymous — Tue, 24 Jul 2012 12:40:36 GMT

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

Re: Substract 2 different rows of a single table

Tue, 24 Jul 2012 12:45:24 GMT

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.

Re: Substract 2 different rows of a single table

Mon, 17 Feb 2014 09:35:08 GMT

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?

Re: Substract 2 different rows of a single table

Mon, 17 Feb 2014 09:41:17 GMT

Thank you Frank its working now for me.

Re: Substract 2 different rows of a single table

Anonymous — Wed, 10 Dec 2014 06:25:51 GMT

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

Re: Substract 2 different rows of a single table

anbu1984 — Wed, 10 Dec 2014 06:39:05 GMT

In Text box,

=Sum(Time1) - Sum(Time2)

In Chart,

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

Re: Substract 2 different rows of a single table

linoyel — Thu, 02 Jul 2015 14:03:52 GMT

Thank you Jonathan!!!

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

Re: Substract 2 different rows of a single table

Anonymous — Fri, 02 Nov 2018 11:04:37 GMT

How to do the same in a KPI ? I want to calculate sum of those differences in a KPI ....

Re: Substract 2 different rows of a single table

mac_hallberg — Thu, 07 Mar 2019 09:33:42 GMT

Thanks a lot, worked perfect!

//Marcus