topic Re: How to hide partial sum if dimension value is null? in QlikView

How to hide partial sum if dimension value is null?

Tue, 01 Jul 2014 10:08:56 GMT

Hi,

I have a pivot table with three dimensions (group, sub group and product). All products are part of a group, but not all products are part of a sub group! In my pivot table I want to show partial sums for sub groups, but only for sub groups that are not null. If I check the 'suppress when value is null' option it suppresses the products that are not part of a sub group. But I want to show them as well. How do I fix this issue?

Re: How to hide partial sum if dimension value is null?

Gysbert_Wassenaar — Tue, 01 Jul 2014 10:15:16 GMT

Can you post an example document with some data?

Re: How to hide partial sum if dimension value is null?

Tue, 01 Jul 2014 10:25:25 GMT

Select that dimension and check the supress null value option in dimension tab

Re: How to hide partial sum if dimension value is null?

Tue, 01 Jul 2014 10:41:38 GMT

If I do this then products that do not have a sub group will be supressed as well (products 1,2 and 5 in screenshot)

Re: How to hide partial sum if dimension value is null?

jonathandienst — Tue, 01 Jul 2014 11:00:32 GMT

It looks like you are using Indent mode for your pivot.I may be mistaken but it may not be possible to do what you want to do in indent mode.

What you can try is to modify your expression to use Dimensionality() to override the partial sum when the sub group is null, something like:

=If(Dimensionality() = 2 And Len([Sub Group] = 0, 0, ... current column expression ....)

I think the subtotal is at Dimensionality 2 but you may have to tweak this to work in your table.

And, of course, enable suppress zero values. If it does not work, try turning off indent mode.

HTH

Jonathan

Re: How to hide partial sum if dimension value is null?

Tue, 01 Jul 2014 11:53:44 GMT

Thank you very much! This works perfectly. (with an extra ')' after [Sub Group] 😉 )

It worked using indent mode, so no need to turn it off.