Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

simonagheo
New Contributor III

Subtotals

Hello,

How can I define subtotals with formula in pivot table?

I have to reproduce the following structure:

LineCodeAmountFormula
110
2200
3100
41101+2-3
560
660
71205+6
810
910
102504+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!

4 Replies
MVP
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)
110
2200
3100
4 310
560
660
7 120
810
910
10 450
simonagheo
New Contributor III

Re: Subtotals

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

briancopple
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
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)
110
2200
3100
4 110
560
660
7 120
810
910
10 250