Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jarno_loubser
Partner - Creator
Partner - Creator

Conditional subtotalling in pivot tables

Hi,

I am trying to only show the subtotal row when more than one row is being totalled – without losing the grant total.

In the example below only Stock Fin Group = MFB should be subtotalled,not all the others because they don’t have different colour combinations.

Capture.JPG

14 Replies
vlad_komarov
Partner - Specialist III
Partner - Specialist III

You can use dimensionality() to show/hide/highlight different levels of the data. Works perfectly in Pivot tables.

dimensionality() = 0 means top level: Grand Total at the bottom of the chart in your case.

Regards,

Vladimir

jarno_loubser
Partner - Creator
Partner - Creator
Author

Perhaps I misunderstand, but Dimensionality will let my decide which expressions are subtotalled, but not which rows within an expression is subtotalled?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

I don't think you can suppress the undesired totals dynamically - it's "all or nothing".

Would make a nice "Idea" for R&D...

If it's "mission critical", I suppose you could generate an artificial "Total" Color in your data model as another dimension value, and assign the subtotal of the other colors of the value, but this looks like way too much work for the sake of an improved presentation...

cheers,

Oleg

vlad_komarov
Partner - Specialist III
Partner - Specialist III

I was suggesting to use dimensionality() in Text Color/Background Color of the Expression to hide numbers you do need to see.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

I think you could hide the unwanted totals by using Dimensionality to select the correct totals level and an expression to count the rows, setting the total to zero when there is only one row. Oh, and suppress zeroes. I have done something similar recently.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jarno_loubser
Partner - Creator
Partner - Creator
Author

Johnothan,

I played with using the noofrows() function and it does "find" the right records for me, but by setting the expression value to zero will I not effect the grant total? I'm only trying to suppress the subtotal (colour) row where n/a, but the grant total must include all values.

jarno_loubser
Partner - Creator
Partner - Creator
Author

In include a mockup of what I need...

Untitled.jpg

jonathandienst
Partner - Champion III
Partner - Champion III

Jarno

Pivot tables do not sum the rows, rather the expression is evaluated at a detail, subtotal or total level. So setting the subtotals to zero should not affect the total if the expression selects the correct level to set to zero. Assuming the subtotal is at level 2, then something like:

=If(Dimensioanlity() <> 2, Sum(....), 0)

Tip: I usually temporarily add a column with expression =Dimensionality() just to determine the correct dimenionality value to use

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jarno_loubser
Partner - Creator
Partner - Creator
Author

Thank you - Getting close now 🙂

if(Dimensionality() = 0 or Dimensionality() = 1 or Dimensionality() = 3 or NoOfRows() > 1,

sum(.....,0)

Is it possible to also suppress the 0 Subtotal rows?

Capture.JPG