Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
anuragsapanbhar
Contributor

Substract 2 different rows of a single table

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.

Untitled.png

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.

1 Solution

Accepted Solutions
Not applicable

Re: Substract 2 different rows of a single table

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.

View solution in original post

11 Replies
sivarajs
Valued Contributor II

Re: Substract 2 different rows of a single table

MVP
MVP

Re: Substract 2 different rows of a single table

Hi

You need something like:

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

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
anuragsapanbhar
Contributor

Re: Substract 2 different rows of a single table

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

Not applicable

Re: Substract 2 different rows of a single table

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.

View solution in original post

Not applicable

Re: Substract 2 different rows of a single table

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?

Not applicable

Re: Substract 2 different rows of a single table

Thank you Frank its working now for me.

bhupaul87
New Contributor

Re: Substract 2 different rows of a single table

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
Honored Contributor III

Re: Substract 2 different rows of a single table

In Text box,

=Sum(Time1) - Sum(Time2)

In Chart,

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

linoybar
Valued Contributor

Re: Substract 2 different rows of a single table

Thank you Jonathan!!!

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