Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rcorcoran
Creator
Creator

null values showing as zero in pivot table

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

9 Replies
sunny_talwar

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.

Capture.PNG

rcorcoran
Creator
Creator
Author

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

Thanks

Rhona

sunny_talwar

Would you be able to share a sample?

rcorcoran
Creator
Creator
Author

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

Rhona

sunny_talwar

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]={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')))

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

Attaching the application for your reference with May Expression updated

Best,

Sunny

rcorcoran
Creator
Creator
Author

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

sunny_talwar

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

rcorcoran
Creator
Creator
Author

Brilliant Sunida

Thank you so much works perfectly.

Rhona

sunny_talwar

Awesome Rhona

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

Best,

Sunny