topic Re: How to suppress Rows in Pivot table but still have values show in subtotals? in QlikView

How to suppress Rows in Pivot table but still have values show in subtotals?

Thu, 28 Jul 2016 17:26:21 GMT

Hi All -

I'm working on a project where I need to essentially suppress certain ROWS from a pivot table where the dimension is equal to specific values. However, I need the expression values on these rows to still show in my subtotals.

I'm able to create a calculated dimension to suppress the specific rows:

=Aggr(Only({$<[Lead Measure Name] -={'Actuals', 'Commitment'}>} [Lead Measure Name]), [Lead Measure Name])

However, I can't seem to figure out how to have the expression sums show in the total. I've tried:

SUM(AGGR(Metric, [Lead Measure Name]))

However this does not work and I cannot use TOTAL or ALL since that then shows the total value on every single row.

An example would be (without the suppression):

Actuals = 3

Commitment = 2

X = 5

Y = 8

Total = 18

Desired Outcome With Suppression:

X = 5

Y = 8

Total = 18

Thanks for any help people can provide!!

Re: How to suppress Rows in Pivot table but still have values show in subtotals?

trdandamudi — Thu, 28 Jul 2016 18:34:39 GMT

See if Dimensionality() or SecondDimensionality() will help. If not can you please post a small sample..

Re: How to suppress Rows in Pivot table but still have values show in subtotals?

Thu, 28 Jul 2016 18:54:15 GMT

I'm not sure how the Dimensionality or SecondDimensionsality functions work. I was having a hard time following the examples provided via the help in QlikView. But maybe I can use them?

Attached is a sample dataset:

The pivot on the left is the full dataset without any row suppression.

The pivot on the right is the pivot with the rows suppressed. However, when you look at the TOTAL for either North or South you can see that the "Actuals" and "Commitments" are not showing up regardless of what I try in the expression.

Thanks!

Re: How to suppress Rows in Pivot table but still have values show in subtotals?

Thu, 28 Jul 2016 18:55:56 GMT

What I'm hoping to see in the Second Pivot is the two rows, but the totals rows show:

North = 22

South = 19

Grand Total = 41

Re: How to suppress Rows in Pivot table but still have values show in subtotals?

sunny_talwar — Thu, 28 Jul 2016 19:25:28 GMT

Try this:

If(Dimensionality() = 2, Sum({$<[Dimension 2] -={'Actuals', 'Commitment'}>}[Metric 1]), Sum([Metric 1]))

Re: How to suppress Rows in Pivot table but still have values show in subtotals?

Thu, 28 Jul 2016 19:32:35 GMT

Thank you! This is exactly what I needed.