Subtotals

[simonagheo]

Hello,

How can I define subtotals with formula in pivot table?

I have to reproduce the following structure:

LineCode	Amount	Formula
1	10
2	200
3	100
4	110	1+2-3
5	60
6	60
7	120	5+6
8	10
9	10
10	250	4+7+8+9

LineCodes 1,2,3,5,6,8,9 exist in my data source, and I have to create lines 4,7,10.

For example: Amount for LineCode4=Amount LineCode1 + Amount LineCode2 - Amount LineCode3.

Thank you!

[swuehl]

Maybe like this:

Table:
LOAD * INLINE [
LineCode, Amount
1, 10
2, 200
3, 100
5, 60
6, 60
8, 10
9, 10
];


LOAD LineCode, LineCode as DimChart
RESIDENT Table;
LOAD LineCode, 4 as DimChart
RESIDENT Table
WHERE Match(LineCode,1,2,3);
LOAD LineCode, 7 as DimChart
RESIDENT Table
WHERE Match(LineCode,5,6);
LOAD LineCode, 10 as DimChart
RESIDENT Table;

Then create a straight table with dimension DimChart and as expression Sum(Amount):

DimChart	Sum(Amount)
1	10
2	200
3	100
4	310
5	60
6	60
7	120
8	10
9	10
10	450

[simonagheo]

Thank you! What can I do with line 3? This line decreases the total for lines 4 and 10...

[Anonymous]

Hi Simona

I think this will give you the result you need. I'm sure it can be refined given time but hopefully it will help you. This is my load script:

Data: load * inline [
LineCode,Amount
1,10 
2,200 
3,100 
5,60 
6,60
8,10 
9,10
];
concatenate (Data) Load 4 as LineCode
resident Data;

concatenate (Data) Load 7 as LineCode
resident Data;

concatenate (Data) Load 10 as LineCode
resident Data;

Then create a pivot table with a single dimension of Line code and create the following expression:

=if(rowno()=4,RangeSum(Above(TOTAL Sum(Amount), 2, 2))-Above(TOTAL Sum(Amount), 1),if(rowno()=7,RangeSum(Above(TOTAL Sum(Amount), 1, 2)),
  if(rowno()=10,RangeSum(Above(TOTAL Sum(Amount), 1, 2))+RangeSum(Above(TOTAL Sum(Amount), 4, 2))+RangeSum(Above(TOTAL Sum(Amount), 8, 2))-Above(TOTAL Sum(Amount), 7),
  Amount)))

Kindest Regards

Brian

[swuehl]

Maybe like this

Table:
LOAD * INLINE [
LineCode, Amount
1, 10
2, 200
3, 100
5, 60
6, 60
8, 10
9, 10
];




LOAD LineCode, LineCode as DimChart, 1 as Factor
RESIDENT Table;


LOAD LineCode, 4  as DimChart,If(LineCode = 3,-1,1) as Factor
RESIDENT Table
WHERE Match(LineCode,1,2,3);


LOAD LineCode, 7 as DimChart, 1 as Factor
RESIDENT Table
WHERE Match(LineCode,5,6);


LOAD LineCode, 10 as DimChart, If(LineCode = 3,-1,1) as Factor
RESIDENT Table
;

DimChart	Sum(Amount*Factor)
1	10
2	200
3	100
4	110
5	60
6	60
7	120
8	10
9	10
10	250