How to Remove Total & Sub Total from One Column in Pivot Table?

[#1]

Hi all,

This is likely a simple problem to solve for the QlikView experts out there.

I am looking for the a way (or the best way) to remove sub totals and the total from one column in a multi-column pivot table.

I searched for some time now, but can't find anyone that has actually needed to remove sub totals from only one column.

The pivot table in question looks something like this:

Product Type	Year	Quarter	Products from Previous Year	New Products	No. Product Sold
A	2011	+	3	2	300
	2012	+	7	2	600
	2013	+	5	3	100
TOTAL			15	7	100
B	2011	+	4	5	100
	2012	+	4	5	200
	2013	+	4	5	300
TOTAL			12	15	600
C	2011	+	2	3	200
	2012	+	2	3	200
	2013	+	2	3	200
TOTAL			6	9	600

How would I therefore go about to remove (or hide) the Totals for the column "Products from Previous Year" while the other Totals are still displayed?

I am still new to QlikView and have inhereted a major project, and trying to find my way still.

Any help you can give would be greatly appreciated!

[swuehl · Accepted solution]

You can use dimensionality() function to check in your expression the level of dimensionality (i.e. if you are in a total or details line), something like

= if (dimensionality() = 2, YOUREXPRESSION)

you'll need to find the correct level of dimensionality for your details line, most easiest by just creating an expression

= dimensionality()

and having a look..

Please refer to the HELP for more details on dimensionality() (and secondarydimensionality() for pivoted dimensions).

Hope this helps,

Stefan

[#3]

Thanks for the quick response !! 🙂

I had a look at dimensionality already and thought it wouldn't help is it would apply for the entire row, whereas I only wanted to remove the Totals for the one column.

Seems I might need to look at it again :-)?

[swuehl]

Not sure what you mean with 'would apply for the entire row', dimensionality() is a function you can use in your expression for column 'Products for Previous Year'. You'll need to use a conditional statement (if() function) to return either your numbers or nothing (like an empty string) based on the dimension level. So you'll essentially just prevent QV from showing the numbers on those (sub-)total lines.

There are lots of samples here in the forum (probably also some in the HELP). If you can post a small sample, I assume someone around will be able to help you right away with the expression needed.

Regards,

Stefan

[#5]

Thanks again Stefan !!!

I got it to work thanks to you !!

I really appreciate your help !!!

- Rene