topic Conditional subtotalling in pivot tables in QlikView

Conditional subtotalling in pivot tables

jarno_loubser — Tue, 15 May 2012 15:28:39 GMT

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.

Conditional subtotalling in pivot tables

vlad_komarov — Tue, 15 May 2012 16:15:05 GMT

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

Conditional subtotalling in pivot tables

jarno_loubser — Tue, 15 May 2012 16:27:32 GMT

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

Conditional subtotalling in pivot tables

Oleg_Troyansky — Tue, 15 May 2012 16:30:35 GMT

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

Conditional subtotalling in pivot tables

vlad_komarov — Tue, 15 May 2012 16:38:17 GMT

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

Conditional subtotalling in pivot tables

jonathandienst — Tue, 15 May 2012 16:44:12 GMT

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

Conditional subtotalling in pivot tables

jarno_loubser — Tue, 15 May 2012 17:03:19 GMT

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.

Conditional subtotalling in pivot tables

jarno_loubser — Tue, 15 May 2012 17:27:50 GMT

In include a mockup of what I need...

Conditional subtotalling in pivot tables

jonathandienst — Tue, 15 May 2012 20:05:32 GMT

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

Conditional subtotalling in pivot tables

jarno_loubser — Wed, 16 May 2012 03:34:04 GMT

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?

Re: Conditional subtotalling in pivot tables

jonathandienst — Wed, 16 May 2012 06:57:00 GMT

In my case, enabling "suppress zero-values" suppressed the subtotals of the level1 dimensions that had no level2 values.

Re: Conditional subtotalling in pivot tables

jonathandienst — Wed, 16 May 2012 06:58:52 GMT

I am assuming that you have no other columns. If you do, then you need to ensure that they are also zero. Remember that suppress zeroes only works if all the expressions in the row are zero or null.

Conditional subtotalling in pivot tables

jarno_loubser — Thu, 24 May 2012 13:41:40 GMT

ok here is the best I could get. Not able to suppress the blank (0) rows, but good enough:

Here is the expression:

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

sum(Amount,'') )

Re: Conditional subtotalling in pivot tables

jonathandienst — Thu, 24 May 2012 14:46:53 GMT

Your expression returns an empty string for the totals. An empty string is not suppressed, so it will display like your totals here. Make sure your expressions return 0 or null to be suppressed, and not an empty string.

Regards

Jonathan

Re: Conditional subtotalling in pivot tables

jarno_loubser — Fri, 25 May 2012 05:25:42 GMT

Hi,

I know that the empty string will not be suppressed, but the zero does not suppress and looks worse. Here it the model - perhaps you can see what is wrong?