topic Re: Create a calculated rows in pivot table in QlikView

Create a calculated rows in pivot table

charles1981 — Wed, 25 Nov 2020 16:16:04 GMT

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

I have one pivot table like below:

Program	Market	Model	Period	Dec-19	Feb-19	Apr-19	Jun-19	Aug-19	Oct-19	Sep-19	Jan-19	Jul-19	May-19	Mar-19	Nov-19	F/Yr Total
P81	Australia	Ca		79421	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25256	19002	19947	30474	21847	22097	22258	16652	19967	23954	24400	22611	268465
Total		104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
Total Market Industry			104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
P82	Australia	Ca		79400	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25201	19002	19947	30474	21847	22097	22288	16302	19967	23954	24400	22611	268145
Total		104601	90993	86265	128980	95010	97840	100336	88510	96037	102752	103718	99655	1194773
Total Market Industry			104601	90993	86265	128980	95010	97840	100336	88510	96037	102752	103718	99655	1194773

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-19	Feb-19	Apr-19	Jun-19	Aug-19	Oct-19	Sep-19	Jan-19	Jul-19	May-19	Mar-19	Nov-19	F/Yr Total
P81	Australia	Ca		79421	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25256	19002	19947	30474	21847	22097	22258	16652	19967	23954	24400	22611	268465
Total		104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
Total Market Industry			104677	90993	86265	128980	95010	97840	100306	88860	96037	102752	103718	99655	1195093
P82	Australia	Ca		79400	71991	66318	98506	73163	75743	78048	72208	76070	78798	79318	77044	926628
Tr		25201	19002	19947	30474	21847	22097	22288	16302	19967	23954	24400	22611	268145
Total		104601	90993	86265	128980	95010	97840	100336	88510	96037	102752	103718	99655	1194773
P81 o/u P82	Australia	Ca		21	0	0
P81 o/u P82	Australia	Tr		55	0	0
P81 o/u P82	Australia	Total		76	0	0

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

Re: Create a calculated rows in pivot table

Sergey_Shuklin — Fri, 31 May 2019 10:15:15 GMT

Hello!

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

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

After that, write your calculated dimension like this:

And expression like this:

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

Re: Create a calculated rows in pivot table

charles1981 — Fri, 31 May 2019 10:38:28 GMT

Thank you so much.

Re: Create a calculated rows in pivot table

Kejti — Fri, 02 Jul 2021 13:16:59 GMT

Hi Sergey!

Do you know is it possible to do that in QS?

I've added the DIM table and first expression - what gives me a total number. But how and where can I add the second expression?

Thanks in advance!

Re: Create a calculated rows in pivot table

Sergey_Shuklin — Fri, 02 Jul 2021 16:20:38 GMT

Hello, Kejti

Yes you can do the same in QS.

You should create dim table as we do in QV:

Then you can create a table:

With dimension:

First value will expand stores, second will be custom calculated.

When you done with dimensions, let's take a look at expressions:

For stores we will simple get sum of sales. But for custom row we use more specific condition - sum only when flag field is equal one.

You should get such result:

If you don't like "-" in the total row, you can handle it with Dimensionality() function:

As you can see, for total row we use some untypical condition as well.

Result table:

Re: Create a calculated rows in pivot table

Kejti — Mon, 05 Jul 2021 11:51:38 GMT

Thanks @Sergey_Shuklin it works perfect, but I need to add more than one calculated row. Not sure, if I should create more DIM islands? Or this solution will work just for the one calc. dimension?

Re: Create a calculated rows in pivot table

Kejti — Mon, 05 Jul 2021 12:43:43 GMT

Ok, I've just added more arguments to the Dim island and it works. Thank you very much!!