topic Subtract two columns in a pivot table in QlikView

Subtract two columns in a pivot table

Wed, 12 Oct 2011 10:19:41 GMT

I have made a pivot table where i need to subtract the two scenarios Budget and Actual to be displayed in a Remaining column.

I have tried to add an expression (which works in my straight tables) but that results in two columns below budget and two below actual. I have also tried to 'Add a calculated dimmension' but that gives me an error.

Project	Actvity	Scenario:	Budget	Actual
Project 1	Activity 1		30	10
	Activity 2		40	30
	Activity 3		10	15
TOTAL			70	55
Project 2	Activity 1		5	5
	Activity 2		25	20
	Activity 3		30	30
TOTAL			60	55
		TOTAL	130	110

Any suggestions?

Thanks!

Re: Subtract two columns in a pivot table

Sokkorn — Wed, 12 Oct 2011 10:32:25 GMT

Hi,

In Remaining expression use this formular = [Budget] - [Actual]

Regards,

Sokkorn

Re: Subtract two columns in a pivot table

Wed, 12 Oct 2011 10:48:10 GMT

When I use the expression:

sum({<Scenario={'Budget'},Amount>}Amount)-sum({<Scenario={'Actuals'},Amount>}Amount)

it creates two columns below Budget and two below Actuals (it doesn't appear like i wish when i drag and drop it somewhere else). Something wrong with my formula?

Subtract two columns in a pivot table

SunilChauhan — Wed, 12 Oct 2011 11:23:27 GMT

column(budgetexpressionno)-column(actualexpressionno)

where expressionno is recognizes like below

for first top expression->column(1)

for second-> column(2)

;;;

so on

Subtract two columns in a pivot table

Tue, 18 Oct 2011 08:03:13 GMT

Back in business!

Sunil,
I have only got 1 expression: if(Scenario ='Budget' or Scenario ='Actuals' ,sum(Amount))

which displays one column with budget and one with actuals.

Is it possible to use COLUMN? Any other way to do it?

Subtract two columns in a pivot table

SunilChauhan — Tue, 18 Oct 2011 08:06:30 GMT

no its not possible

but You can write like below to substract

sum( if(Scenario ='Budget' ,Amount))

sum( if(Scenario ='Actuals',Amount))

Subtract two columns in a pivot table

Tue, 18 Oct 2011 08:38:00 GMT

If i use:

sum( if(Scenario ='Budget' ,Amount)) - sum( if(Scenario ='Actuals',Amount))

I end up with 4 columns:

Budget | Budget - 0| Actuals | 0 - Actuals

when I want:

Budget | Actuals | Remaining

Hmmm.....

Subtract two columns in a pivot table

SunilChauhan — Tue, 18 Oct 2011 08:50:23 GMT

in first expression

sum( if(Scenario ='Budget' ,Amount)) fro Budget

in second expression

sum( if(Scenario ='Actuals',Amount)) for Actual

in third expression

sum( if(Scenario ='Budget' ,Amount)) - sum( if(Scenario ='Actuals',Amount))

or if want like

Budget | Actuals | Remaining

in same expression

sum( if(Scenario ='Budget' ,Amount)) &'|'&sum( if(Scenario ='Actuals',Amount)) &'|' &

sum( if(Scenario ='Budget' ,Amount)) - sum( if(Scenario ='Actuals',Amount))

Subtract two columns in a pivot table

Tue, 18 Oct 2011 08:59:39 GMT

Thanks Sunil! You have been very helpful and patient!