# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor III

## Subtotals

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!

Tags (3)
4 Replies MVP

## Re: Subtotals

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

## Re: Subtotals

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

Contributor II

## Re: Subtotals

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 MVP

## Re: Subtotals

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