Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
It is set to – on the presentation tab and no matter what I set this to it still shows zeros.
Thanks
Rhona
Would you be able to share a sample?
I will post a test one now where I want the same thing thanks
Rhona
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
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
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
Brilliant Sunida
Thank you so much works perfectly.
Rhona
Awesome Rhona
I am glad that we were able to resolve the issue.
Best,
Sunny