Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

charles1981
New Contributor II

Create a calculated rows in pivot table

Hi, can anyone please help me to achieve the below:

I have one pivot table like below:

Program Market Model Period Dec-19Feb-19Apr-19Jun-19Aug-19Oct-19Sep-19Jan-19Jul-19May-19Mar-19Nov-19F/Yr Total 
P81AustraliaCa 794217199166318985067316375743780487220876070787987931877044926628
Tr 252561900219947304742184722097222581665219967239542440022611268465
Total  104677909938626512898095010978401003068886096037102752103718996551195093
Total Market Industry   104677909938626512898095010978401003068886096037102752103718996551195093
P82AustraliaCa 794007199166318985067316375743780487220876070787987931877044926628
Tr 252011900219947304742184722097222881630219967239542440022611268145
Total  104601909938626512898095010978401003368851096037102752103718996551194773
Total Market Industry   104601909938626512898095010978401003368851096037102752103718996551194773

 

Now I want to create a additional calculated row in the above table to find difference of each month between the programs (first column) as below

Program Market Model Period Dec-19Feb-19Apr-19Jun-19Aug-19Oct-19Sep-19Jan-19Jul-19May-19Mar-19Nov-19F/Yr Total 
P81AustraliaCa 794217199166318985067316375743780487220876070787987931877044926628
Tr 252561900219947304742184722097222581665219967239542440022611268465
Total  104677909938626512898095010978401003068886096037102752103718996551195093
Total Market Industry   104677909938626512898095010978401003068886096037102752103718996551195093
P82AustraliaCa 794007199166318985067316375743780487220876070787987931877044926628
Tr 252011900219947304742184722097222881630219967239542440022611268145
Total  104601909938626512898095010978401003368851096037102752103718996551194773
P81 o/u P82AustraliaCa 2100          
P81 o/u P82AustraliaTr 5500          
P81 o/u P82AustraliaTotal  7600          

like above I want to create calculated row P81 o/u P82 (P81->Australia->Ca - P82->Australia->Ca) for all the months.

 

1 Solution

Accepted Solutions
Highlighted
Sergey_Shuklin
Valued Contributor

Re: Create a calculated rows in pivot table

Hello!

There is a solution. Please, see at this short instace:

First of all you need to load additional table in the script

diff_pivot_rows_pic1.png

After that, write your calculated dimension like this:

diff_pivot_rows_pic3.png

And expression like this:

diff_pivot_rows_pic2.png

This way will calculate sum for P* rows for dim = 1 and difference of P* rows for dim = 2.

2 Replies
Highlighted
Sergey_Shuklin
Valued Contributor

Re: Create a calculated rows in pivot table

Hello!

There is a solution. Please, see at this short instace:

First of all you need to load additional table in the script

diff_pivot_rows_pic1.png

After that, write your calculated dimension like this:

diff_pivot_rows_pic3.png

And expression like this:

diff_pivot_rows_pic2.png

This way will calculate sum for P* rows for dim = 1 and difference of P* rows for dim = 2.

charles1981
New Contributor II

Re: Create a calculated rows in pivot table

Thank you  so much.