topic Re: null values showing as zero in pivot table in QlikView

null values showing as zero in pivot table

rcorcoran — Wed, 03 Jun 2015 08:32:32 GMT

Hi I need the expressions below to only show if value is not null. at the moment it is showing zero in the columns that have no data/missing. It should only show zero if the value is actually zero.

Thanks

Pick(Match(ValueList('Admission Avoidance (includes OPAT) ','Hospital Avoidance',' Early discharge (includes OPAT)', 'Other'),
'Admission Avoidance (includes OPAT) ','Hospital Avoidance',' Early discharge (includes OPAT)', 'Other'),

num((((Sum({$<[Community Intervention Team by Source]={5,6},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Sum({$<[Community Intervention Team by Source]={7,10,12},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Sum({$<[Community Intervention Team by Source]={8,9},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Sum({$<[Community Intervention Team by Source]={11,13}, ReferredMM={1}>}ActualValue)))), '##0'))

I have attached a sample table where I want this but have test data. It is the same thing though. I don't want the zeros to show for months with no data or missing data

Re: null values showing as zero in pivot table

sunny_talwar — Wed, 03 Jun 2015 10:01:30 GMT

What is the Null Symbol and/or missing Symbol you are using on the presentation tab of chart properties? Is it by any chance set to 0?? If it is, change it to '-' may be or just a blank.

Re: null values showing as zero in pivot table

rcorcoran — Wed, 03 Jun 2015 10:09:27 GMT

It is set to – on the presentation tab and no matter what I set this to it still shows zeros.

Thanks

Rhona

Re: null values showing as zero in pivot table

sunny_talwar — Wed, 03 Jun 2015 10:10:15 GMT

Would you be able to share a sample?

Re: null values showing as zero in pivot table

rcorcoran — Wed, 03 Jun 2015 10:14:51 GMT

I will post a test one now where I want the same thing thanks

Rhona

Re: null values showing as zero in pivot table

sunny_talwar — Wed, 03 Jun 2015 10:25:52 GMT

What if you write a expression like this for May:

If(Pick(Match(ValueList('test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8')

'test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8'),

' ',

num((((Sum({$<[POA]={1,2},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={1},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,29},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={4,6,8,10,12,14,16,18,20,22,24,26,28,30},ReferredMM={5}>}ActualValue)))), '##0'),

num(Sum({$<[POA]={4,6,8,10,12,14,16,18,20,22,24,26,28,30},ReferredMM={5}>}ActualValue)/Sum({$<[POA]={3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},ReferredMM={5}>}ActualValue), '##0%'),

num((((Sum({$<[POA]={31,32,33},ReferredMM={5}>}ActualValue)))), '##0')) <> 0,

Pick(Match(ValueList('test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8')

'test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8'),

' ',

num((((Sum({$<[POA]={1,2},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={1},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,29},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={4,6,8,10,12,14,16,18,20,22,24,26,28,30},ReferredMM={5}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={31,32,33},ReferredMM={5}>}ActualValue)))), '##0')))

Basically saying if If(YourExpression <> 0, YourExpression, Null())

Attaching the application for your reference with May Expression updated

Best,

Sunny

Re: null values showing as zero in pivot table

rcorcoran — Wed, 03 Jun 2015 10:46:39 GMT

Hi Sunida

This works to take away the zeros but what if the value should be 0. Can you do if <> null ?? Or something like that

Thanks

Rhona

Re: null values showing as zero in pivot table

sunny_talwar — Wed, 03 Jun 2015 11:03:00 GMT

Here disregard my previous formula, Try this new one:

Test it on Jan and May, for Jan it would be:

If(IsNull(Pick(Match(ValueList('test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8')

'test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8'),

' ',

num((((Avg({$<[POA]={1,2},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Avg({$<[POA]={1},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Avg({$<[POA]={3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Avg({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,29},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Avg({$<[POA]={4,6,8,10,12,14,16,18,20,22,24,26,28,30},ReferredMM={1}>}ActualValue)))), '##0'),

num(Sum({$<[POA]={4,6,8,10,12,14,16,18,20,22,24,26,28,30},ReferredMM={1}>}ActualValue)/Sum({$<[POA]={3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},ReferredMM={1}>}ActualValue), '##0%'),

num(Avg({$<[POA]={31,32,33},ReferredMM={1}>}ActualValue), '##0'))), Null(),

Pick(Match(ValueList('test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8')

'test1',

'test2',

'test3',

'test4',

'test5',

'test6',

'test7',

'test8'),

' ',

num((((Sum({$<[POA]={1,2},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={1},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={3,5,7,9,11,13,15,17,19,21,23,25,27,29},ReferredMM={1}>}ActualValue)))), '##0'),

num((((Sum({$<[POA]={4,6,8,10,12,14,16,18,20,22,24,26,28,30},ReferredMM={1}>}ActualValue)))), '##0'),

num(Sum({$<[POA]={4,6,8,10,12,14,16,18,20,22,24,26,28,30},ReferredMM={1}>}ActualValue)/Sum({$<[POA]={3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},ReferredMM={1}>}ActualValue), '##0%'),

num(Sum({$<[POA]={31,32,33},ReferredMM={1}>}ActualValue), '##0')))

Please note the bold part of the expression, I am checking if the Avg isNull(), then null, other give me the sum. The reason I am checking avg for null is because sum is giving me 0 instead of null when ReferredMM doesn't exist, whereas Avg will output null if something is missing whereas if it is 0, it will give you 0 avg.

Try and see if this one is helpful.

Best,

Sunny

Re: null values showing as zero in pivot table

rcorcoran — Wed, 03 Jun 2015 11:10:39 GMT

Brilliant Sunida

Thank you so much works perfectly.

Rhona

Re: null values showing as zero in pivot table

sunny_talwar — Wed, 03 Jun 2015 11:48:38 GMT

Awesome Rhona

I am glad that we were able to resolve the issue.

Best,

Sunny