topic Re: Subtract upper row in a pivot table in QlikView

Subtract upper row in a pivot table

Thu, 22 May 2014 09:49:42 GMT

Hi guys!

I'm new in this community and I hope to find a solution to my issue.

I have a pivot table like this

Month	A	B	X
1	10	0	0
2	5	2	8
3	20	1	4

where only Month is a dimension and A and X are expressions.

I want to evaluate X in this mode X(Month) = A(Month - 1) - B(Month).

Can someone explain me how to do that?

Re: Subtract upper row in a pivot table

tresB — Thu, 22 May 2014 09:52:30 GMT

Try:

=Above(A)-B

A,B could be labels or expressions.

Re: Subtract upper row in a pivot table

vipin_mishra479 — Thu, 22 May 2014 09:56:14 GMT

use above function like tresco has define.

=Above(A)-B

Re: Subtract upper row in a pivot table

Thu, 22 May 2014 09:58:48 GMT

Hi tresesco,

I'm sorry but it doesn't work. It returns '-' as result.

Re: Re: Subtract upper row in a pivot table

tresB — Thu, 22 May 2014 10:04:42 GMT

See attached qvw

Re: Subtract upper row in a pivot table

Thu, 22 May 2014 10:19:59 GMT

Your example is perfect but I don't understand why it doesn't work for my chart.

See the attached screenshot (the formula is at the 6th line).

The expression is related to the column "Storno totale".

Re: Re: Subtract upper row in a pivot table

tresB — Thu, 22 May 2014 10:45:22 GMT

Sum ( If( Sum ......... structure is wrong. To use one aggregation function into another, you have to use Aggr(), like:

=Aggr( Sum( If(Sum....)), Dim1, Dim2...)

Re: Subtract upper row in a pivot table

SunilChauhan — Thu, 22 May 2014 10:48:17 GMT

sum(above(A))-Sum(B)

Re: Subtract upper row in a pivot table

Thu, 22 May 2014 10:53:42 GMT

Use Above(A)-B

Re: Subtract upper row in a pivot table

Thu, 22 May 2014 11:20:52 GMT

Can you send some sample.qvw

Re: Re: Subtract upper row in a pivot table

Thu, 22 May 2014 13:30:53 GMT

Ok,

I made a simple example too. Let me make a rewind and explain the case in more details.

The structure of my table is a little bit more complex as you can see in the attached qvw.

My goal is to evaluate the X column as the sum of A and B of the previous month only if the parameters P1 and P2 are 0.

In the qvw I'm evaluating X using set analysis but it (obviously) works only if a month is selected. I need to evaluate X with the explained rules disregarding selections on month.

In the subsequent table the final result that I must reach

Month	P1	P2	Key	In	Out	A	B	X
1	0	0	A	0	10	10	0	-
1	0	0	B	0	4	20	5	-
1	1	1	A	10	0	0	0	0
1	1	1	B	4	0	0	0	0
2	0	0	A	0	20	5	2	10
2	0	0	B	0	4	30	10	25
2	0	1	A	8	0	0	0	0
2	0	1	B	3	0	10	5	0
2	1	0	A	10	0	0	0	0
2	1	1	A	2	0	0	0	0
3	0	0	B	0	30	20	1	40
3	0	1	B	20	0	0	0	0
3	1	0	B	10	0	0	0	0

Thanks to all and sorry for imprecision.

Month	P1	P2	Key	In	Out	A	B	X
1	0	0	A	0	10	10	0	-
1	0	0	B	0	4	20	5	-
1	1	1	A	10	0	0	0	0
1	1	1	B	4	0	0	0	0
2	0	0	A	0	20	5	2	10
2	0	0	B	0	4	30	10	25
2	0	1	A	8	0	0	0	0
2	0	1	B	3	0	10	5	0
2	1	0	A	10	0	0	0	0
2	1	1	A	2	0	0	0	0
3	0	0	B	0	30	20	1	40
3	0	1	B	20	0	0	0	0
3	1	0	B	10	0	0	0	0

Month	P1	P2	Key	In	Out	A	B	X
1	0	0	A	0	10	10	0	-
1	0	0	B	0	4	20	5	-
1	1	1	A	10	0	0	0	0
1	1	1	B	4	0	0	0	0
2	0	0	A	0	20	5	2	10
2	0	0	B	0	4	30	10	25
2	0	1	A	8	0	0	0	0
2	0	1	B	3	0	10	5	0
2	1	0	A	10	0	0	0	0
2	1	1	A	2	0	0	0	0
3	0	0	B	0	30	20	1	40
3	0	1	B	20	0	0	0	0
3	1	0	B	10	0	0	0	0

Month	P1	P2	Key	In	Out	A	B	X
1	0	0	A	0	10	10	0	-
1	0	0	B	0	4	20	5	-
1	1	1	A	10	0	0	0	0
1	1	1	B	4	0	0	0	0
2	0	0	A	0	20	5	2	10
2	0	0	B	0	4	30	10	25
2	0	1	A	8	0	0	0	0
2	0	1	B	3	0	10	5	0
2	1	0	A	10	0	0	0	0
2	1	1	A	2	0	0	0	0
3	0	0	B	0	30	20	1	40
3	0	1	B	20	0	0	0	0
3	1	0	B	10	0	0	0	0