topic Split Subtotals in pivot table in QlikView

Split Subtotals in pivot table

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

Hello,

is there any way to split the subtotals by a sub-dimension in a pivot table?

For example:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8
	MONTH	JAN	JAN	FEB	FEB	TOTAL	TOTAL
TYPE	YEAR	2016	2017	2016	2017	2016	2017
CAR		10	20	15	25	25	45
BIKE		1	4	10	0	11	4
HOUSE		2	5	8	15	10	20

If I select to compute subtotals for the dimension "MONTH", I can only get the total sum of the row (2016+2017), which is meaningless to me. Also, I don't want to switch YEAR and MONTH dimension, because I'd like to do a comparison by months.

I've also attached a .qvw as an example of what I'm trying to achieve.

Any help will be really appreciated.

Re: Split Subtotals in pivot table

sunny_talwar — Mon, 31 Jul 2017 16:16:30 GMT

May be use Pick(Match approach....

Re: Split Subtotals in pivot table

jekbauita — Tue, 01 Aug 2017 12:09:40 GMT

Hi Sunny, thanks for your reply, it's really helpful. I think I understood the way it works, does it mind to point me to some resource that can help me with a better understanding of the Pick(Match approach?

Thanks!

Re: Split Subtotals in pivot table

sunny_talwar — Tue, 01 Aug 2017 12:15:14 GMT

I don't think this approach is documented yet, but there are multiple other examples on the community where I have proposed this way of doing things in bar chart, pivot tables etc... if you want I can share those links?

Re: Split Subtotals in pivot table

jekbauita — Tue, 01 Aug 2017 12:16:56 GMT

I think it would be great for someone like me wh would like to understand the mechanism behind the scene.

Re: Split Subtotals in pivot table

sunny_talwar — Tue, 01 Aug 2017 12:20:24 GMT

Here are some examples

Stacked and Grouped in a single bar chart?

Chart with Dimension of "Today", "current month" and "current month of last year"‌

Pivot problem for adding another dimension | Qlik Community

Re: Split Subtotals in pivot table

Kushal_Chawda — Tue, 01 Aug 2017 12:21:17 GMT

bhai pehle hi bola tha tuje ki ispe blog likh

Re: Split Subtotals in pivot table

sunny_talwar — Tue, 01 Aug 2017 12:23:30 GMT

bhai website le li hai...bas blog likhna shuru karna hai.

Re: Split Subtotals in pivot table

Kushal_Chawda — Tue, 01 Aug 2017 12:37:46 GMT

kab start karega. people are waiting

Re: Split Subtotals in pivot table

sunny_talwar — Tue, 01 Aug 2017 12:38:50 GMT

Bahut jald

Re: Split Subtotals in pivot table

jekbauita — Tue, 01 Aug 2017 12:41:12 GMT

Still don't know if I've understood the matter properly, just for the benefit of the topic I will try to sum it up:

create a new dimension as a linear table within the load script. Values of the table will be just 1 and 2;
create a new calculated dimension as "=Pick(Dim, DIMENSION, 'Total')" where:
- Dim is the linear dimension
- DIMENSIONis the 'parent' dimension - the one we want to be split
- 'Total' is the label you wnat to give to the "total" at the bottom/start of the table - hadn't tried myself, but I think you can put the total at the begin of the table by just switching the order between COLUMN and 'Total' within the Pick function
create a new expression in the expression tab (subtotals should not be selected)

Alla other options in the .qvw file are just for formatting reasons.

The trick mechanic is (or at least I think it works because):

loading a new linear dimension forces QlikView to doble the rows in the loaded data;
DIMENSION is now computed and QlikView forces it to be:
- DIMENSION, when label is 1
- A simple label when label is 2: due to the fact that data is doubled, the returned value is 'Total' and the data is equal to all the choosen repository. Data can also be divided in all the desired sub-dimension.

My question is: in terms of performance does this affect badly my QlikView application? Are there any disvantages due to the fact that data are doubled? Maybe in a big application you need some precautions when using this tecnique?

Re: Split Subtotals in pivot table

sunny_talwar — Tue, 01 Aug 2017 13:40:36 GMT

Look at my responses in red below

Giacomo Berdondini wrote:

Still don't know if I've understood the matter properly, just for the benefit of the topic I will try to sum it up:

create a new dimension as a linear table within the load script. Values of the table will be just 1 and 2;

create a new calculated dimension as "=Pick(Dim, DIMENSION, 'Total')" where:

Dim is the linear dimension

DIMENSIONis the 'parent' dimension - the one we want to be split

'Total' is the label you wnat to give to the "total" at the bottom/start of the table - hadn't tried myself, but I think you can put the total at the begin of the table by just switching the order between COLUMN and 'Total' within the Pick function

To change the location of Total, I suggest using the sort tab. You can also bring it in the middle or wherever it would make sense for you to see it.

create a new expression in the expression tab (subtotals should not be selected)

That is correct

Alla other options in the .qvw file are just for formatting reasons.

The trick mechanic is (or at least I think it works because):

loading a new linear dimension forces QlikView to doble the rows in the loaded data;

This is not true... we are not doubling the data here. Dim is just a island table which won't impact your other tables.

DIMENSION is now computed and QlikView forces it to be:

DIMENSION, when label is 1

That is right

A simple label when label is 2: due to the fact that data is doubled, the returned value is 'Total' and the data is equal to all the choosen repository. Data can also be divided in all the desired sub-dimension.

Not sure I understand this

My question is: in terms of performance does this affect badly my QlikView application? Are there any disvantages due to the fact that data are doubled? Maybe in a big application you need some precautions when using this tecnique?

There will definitely some impact to performance, but it is going to be negligible to little based on the size of the application. Also, like I mentioned that you are not doubling the data, so there are no disadvantages there. I can't think of any precautions, but you might need to modify it if you need to make changes. For instance if you need DIMENSION, Total and Avg, then you will need to increase the value of Dim from 1 and 2 to 1, 2, and 3. and there can be other changes that you might need.

Re: Split Subtotals in pivot table

jekbauita — Tue, 01 Aug 2017 14:53:21 GMT

Ok, thank you Sunny, it really helped me understanding some of the QlikView mechanics.

Thank you for your time.

Giacomo

Re: Split Subtotals in pivot table

sunny_talwar — Tue, 01 Aug 2017 14:56:24 GMT

No problem at all

I am glad I was able to offer some help here

Re: Split Subtotals in pivot table

jekbauita — Tue, 01 Aug 2017 16:52:59 GMT

Another problem...

If I'm using a set expression to limit result in the "Total" expression, and thank if I want to use an additional calculated espression (for example to compute the percentage growth from one year to the next one), all rows are shown with 0 value (even the ones outside the selection of my set expression).

I've attached a file to make more clear what I'm trying to achieve

Re: Split Subtotals in pivot table

sunny_talwar — Tue, 01 Aug 2017 16:57:24 GMT

Like I mentioned above, you can further use the Pick match technique to work on this... Just so I understand, you are looking to only show diff where there is a value? remove where it is null?

Re: Split Subtotals in pivot table

jekbauita — Tue, 01 Aug 2017 21:07:16 GMT

Yes, following the example above I would like to have the % difference, for each month, between the 2016 and the 2017 value. I tried to play around Pick and Match but couldn't get any result as of now.

Re: Split Subtotals in pivot table

sunny_talwar — Wed, 02 Aug 2017 01:37:40 GMT

This?

Re: Split Subtotals in pivot table

jekbauita — Wed, 02 Aug 2017 02:47:29 GMT

That is definetly what I was trying to achive, I'm going to play around it a little to make it work properly for my application.

Thank you again.

Re: Split Subtotals in pivot table

sunny_talwar — Wed, 02 Aug 2017 16:17:17 GMT

I am glad it worked