Subtract subtotals in pivot table

Thu, 08 Apr 2010 18:59:57 GMT

Hi all,

I've got a problem to subtract subtotal in a pivot table if a have dimension in rows and in columns. It is requirement often in finical P&L analysis.
As long as I have just 1 dimensions, the formula

sum({$<Parent={'1 Sales'}>} total (Value))

is working perfectly (Attached file complex Pivot /Pivot 2).

As soon as I have to add an additional dimension in column order, the "total" statement is calculated in each column by the overall subtotal by row instead of subtotal by row and column. (Attached file complex Pivot /Pivot 3).

Subtotals should be calculated as following. Since regular analysis needs to be done by several people, all using QV, a direct QV solution is much handier than an Export into Excel.

SOE	Parent	Period	2010 01	2010 02	Gesamt
1 Margin	1 Sales		-600	-1200	-1800
1 Margin	2 Standard COS		180	360	540
1 Margin	Subtotal (1 Margin)		-420	-840	-1260
2 Adj. Margin	3 Other COS		30	60	90
2 Adj. Margin	4 Variance		45	90	135
2 Adj. Margin	Subtotal (1 Margin - 2 Adj. Margin)		-345	-690	-1035
3 EBIT	5 G&A		60	120	180
3 EBIT	6 Selling		75	150	225
3 EBIT	7 Marketing		90	180	270
3 EBIT	8 Other Other above		105	210	315
3 EBIT	Subtotal (2 Adj. Margin - 3 EBIT)		-15	-30	-45
4 Net Income	9 Other below		10	20	30
4 Net Income	Subtotal (3 EBIT - 4 Net Income)		-5	-10	-15
Gesamt			-5	-10	-15

It would be great, if somebody could give me a hint how to handle this problem.

Many Thanks

Werner

Subtract subtotals in pivot table

pover — Thu, 08 Apr 2010 19:50:42 GMT

Werner,

In order to define a total that respects the scope of a dimension use the following syntax:

sum(total <dimension> value)

The example is in the pivot table prueba in the attached file.

Pover

Subtract subtotals in pivot table

Thu, 08 Apr 2010 21:02:36 GMT

Many Thanks ! It is working perfectly!

topic Subtract subtotals in pivot table in QlikView

Subtract subtotals in pivot table

Subtract subtotals in pivot table

Subtract subtotals in pivot table