topic Re: How to force null value into pivot table line in QlikView

How to force null value into pivot table line

Fri, 24 Jul 2015 16:35:07 GMT

Hi,

I'd like to ask a little help.

I have a pivot chart like this one below:

Product	MeasureA	MeasureB
A Product	2	3
B Product	3	5
C Product	4	6
Total	9	-

MeasureA and MeasureB are sum(<set analysis>Volume) expressions.

I have to show null value in MeasureB column for total.

Does somebody know a working solution for this? I tried with Product -= {''Total'} in set analysis but it shows zero value in the total cell, but I need null value.

Thank you

Gabor

Re: How to force null value into pivot table line

sunny_talwar — Fri, 24 Jul 2015 16:37:15 GMT

Try using the dimensionality() function for this:

If(Dimensionality() = 1, Null(), YourExpression)

If(Dimensionality() = 0, Null(), YourExpression)

Re: How to force null value into pivot table line

sunny_talwar — Fri, 24 Jul 2015 16:38:27 GMT

Is total a row from the database or is a total you calculate in the chart?

Re: How to force null value into pivot table line

sunny_talwar — Fri, 24 Jul 2015 16:41:06 GMT

This?

Re: How to force null value into pivot table line

Fri, 24 Jul 2015 16:42:34 GMT

From the database. All of the Production columns value has dimensionality 1.

Re: How to force null value into pivot table line

Fri, 24 Jul 2015 16:44:32 GMT

Total is an element from the Product. The dimensionality functions does not work in that case.

Re: How to force null value into pivot table line

sunny_talwar — Fri, 24 Jul 2015 16:45:43 GMT

Try this expression:

=If(Sum({<Product -= {'Total'}>}MeasureB) = 0, Null(), Sum({<Product -= {'Total'}>}MeasureB)).

Re: How to force null value into pivot table line

Fri, 24 Jul 2015 16:52:01 GMT

Thank you. The solution is closer, but it shows null instead of 0 if A Product = 0, but I need null only in the total row. Do you have an idea?

Re: How to force null value into pivot table line

sunny_talwar — Fri, 24 Jul 2015 16:59:51 GMT

Try this expression:

=If(Product = 'Total', Null(), Sum({<Product -= {'Total'}>}MeasureB))

Output:

Re: How to force null value into pivot table line

Fri, 24 Jul 2015 17:29:44 GMT

Perfect! Thank you!

Re: How to force null value into pivot table line

sunny_talwar — Fri, 24 Jul 2015 17:34:59 GMT

Awesome

I am glad it worked.

Best,

Sunny