topic Re: Show null value in pivot table instead of 0 for specified row in QlikView

Show null value in pivot table instead of 0 for specified row

Tue, 28 Jul 2015 21:15:09 GMT

Hi All,

I have a table which contains several reports (left table). I have to create a pivot table (right one), and I need to show only ReportA but I have to show zero values also and the Total should be null value. (Total is a row in the table and not necessary the total of the Products)

I have created an if statement (if(Product='Total', null(), sum(....)).

I remove the tick from the checkbox on Presentation tab named Suppress Zero values.

But when I did this ReportB appears in my pivot table as you can see on the right table. Is there any solution if I want to show null value for the specified row but I have to use set analysis in the expression?

Thanks for you time and help!

Gabor

Re: Show null value in pivot table instead of 0 for specified row

srchagas — Tue, 28 Jul 2015 21:27:17 GMT

Hi Could You Attach a Sample ?

Re: Show null value in pivot table instead of 0 for specified row

Tue, 28 Jul 2015 21:29:30 GMT

Sure!

Re: Show null value in pivot table instead of 0 for specified row

awhitfield — Wed, 29 Jul 2015 10:38:33 GMT

Hi,

why are you importing the TOTAL value as a Dimension?

1.Don't load the TOTAL product

2. Enable Partial Sums

3. Change expression to Sum({<Report = {'ReportA'}>}MeasureB)

And you get this:

Andy

Re: Show null value in pivot table instead of 0 for specified row

Wed, 29 Jul 2015 11:41:15 GMT

Hi,

As I wrote earlier the Total Product not necessary equal with sum of products.

And in the Total Product row I have to show null value as you can see in the example.

Thanks

Gabor

Re: Show null value in pivot table instead of 0 for specified row

sasiparupudi1 — Wed, 29 Jul 2015 12:46:42 GMT

Please see if the attached file helps?

Re: Show null value in pivot table instead of 0 for specified row

sgrice — Wed, 29 Jul 2015 12:55:46 GMT

change this

(if(Product='Total', null(), sum(....))

(if(Product='Total', '-', sum(....)).

Re: Show null value in pivot table instead of 0 for specified row

Wed, 29 Jul 2015 13:20:07 GMT

Thank you very much! It works the way how I'd like: