Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
RickF
Partner - Contributor II
Partner - Contributor II

Columns Subtotals in qlik sense pivot table

Hi all,

I have a sheet where a pivot table is used with 2 dimension and 3 measures.

In current setting, I have 1 dimension on rows and 1 dimension and all 3 measure on columns and have Column totals displayed.

What I'd like to have is a column subtotal.

Let's say mi dimension has 3 possible values A, B and C.
Now I have measures displayed for A, B, C and total.
I want to have another column which shows subtotal measure for A+B, is there any way to do so?

Labels (1)
2 Replies
julian_rodriguez
Partner - Specialist
Partner - Specialist

In Qlik Sense, creating subtotals for specific dimension values in a pivot table (like summing up values A + B and excluding C) directly within the table isn't a built-in feature. However, you can achieve this by using calculated dimensions or measure expressions that specifically include the dimension values you want to sum.

Here's a general approach to create a measure that would only sum the values for A and B:

1. **Create an Inline Table in the Load Script**: You can create an inline mapping table in your load script that assigns a group to dimension values A and B, and another group to C.

```qlik
GroupMapping:
MAPPING LOAD * INLINE [
YourDimension, Group
A, AB
B, AB
C, C
];

LOAD
...
ApplyMap('GroupMapping', YourDimension, 'Other') as Group,
...
```

2. **Use Set Analysis in Your Measure**: Use set analysis in your measure to only sum values where the Group is AB.

```qlik
Sum({<Group={'AB'}>} YourMeasure)
```

3. **Add as a New Measure**: Add this as a new measure in your pivot table to get the subtotal for A and B.

4. **Pivot Table Setup**: Adjust your pivot table to have this new measure alongside your existing measures. You might need to restructure your pivot table to ensure that the dimensions and measures line up correctly to display this subtotal.

Please note that `YourDimension` should be replaced with the actual dimension name, and `YourMeasure` should be replaced with the actual measure field in your data model.

This method requires an understanding of your data model and the way your pivot table is set up. If you need more specific guidance, you would need to provide more details about your data and the current setup of your pivot table.

Remember, Qlik Sense is quite flexible with expressions, and there might be other ways to achieve this, such as complex set analysis expressions or even calculated dimensions. However, the general principle would remain to segment your data either in the load script or within the expression itself.

RickF
Partner - Contributor II
Partner - Contributor II
Author

I figured out how to work on my fields and I managed to add a dimension on columns in order to obtain what I need as total of the second dimension on pivot table's columns.

A new field has been added with value A+B for original A or B field value and C all other cases.

This new field has been added on the pivot table as first column dimension and original value has been added as second dimension on column.
Using totals on second dimension results on the value I needed as A+B.

Problem now is that I'd like not having totals for C value, if possible.