Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

customized grouping in pivot-tables

Hi everybody,

I am currently working with QV  and need to migrate former reports from excel. A common issue that has not been solved yet is the implementation of 'customized' subtotals in pivot tables.

For example: For certain clusters or product segments I'd like to show subtotals in our pivot table (see attachment). Is there any option to group dimensions individually within QV?

Microsoft Excel.png

I am aware of the possibility to map the clusters/product segments beforehand and to add another level to the dimension. Then we could show the subtotals for this new dimension. However, I consider this option as not nice - especially in large pivot charts.

Any tips & tricks?

Any information would me much appreciated.

Best regards,

Tobias

1 Solution

Accepted Solutions
Highlighted
Not applicable

Hi Tobias,

if you need these within a single dimension, you could potentially add a side dimension grouping for your country field, adding in the required sub total values you require against the corresponding countries.

This would side separately to your main fact table and so your standard expressions should work fine for it.

you can then add some logic to the dimension and expression format to bold the rows you want.

Have a look at the attached, should give you the idea of what I mean

hope that helps

Joe

View solution in original post

5 Replies
Highlighted
Not applicable

Hi Tobias,

this could be a long shot in the dark. try to tick the "Dropdown select" and "Show Partial Sums", under the "Presentations" sheet in your Chart properties. selected it for every expression.

Hope it helps.

Highlighted
MVP & Luminary
MVP & Luminary

If it should be in one single dimension you will need additionally dimension-items within your data-model:

concatenate (CountryDimensionTable)

Load * Inline [

Country

Sum America

Sum Asia

...

];

Maybe you used an extra Country field for this - Country as CountryWithPartialSumDummy - to keep your main field clean. Then within the expressions you need to query these Country field if it is a partial sum and when it is then use some set analysis statements like: if(Country = 'Sum ...', sum({< Country = {'x', 'y', 'z'}>} value), sum(value)).

But with an additionally Country group and native partial sums would it be easier and such Country group could be very small, maybe only a number or a single char.

- Marcus

Highlighted
Not applicable

Hi Tobias,

if you need these within a single dimension, you could potentially add a side dimension grouping for your country field, adding in the required sub total values you require against the corresponding countries.

This would side separately to your main fact table and so your standard expressions should work fine for it.

you can then add some logic to the dimension and expression format to bold the rows you want.

Have a look at the attached, should give you the idea of what I mean

hope that helps

Joe

View solution in original post

Highlighted
Not applicable

Hi Marcus,

Even though Joe's input already helps me out, I'd like to understand your proposal. How can I add this extra country field and query this field?

BR Tobias

Highlighted
MVP & Luminary
MVP & Luminary

I meant simply to add a few dummy-rows to your dimension (maybe per xls or inline-load) with the result that country had not only real values from a fact- or a dimension-table else dummy-values like "Sum XYZ". And within the chart you queries these rows with an expression like above mentioned.

But better is you used normal hierachical dimensions with partial sums instead a customized view.

- Marcus