9 Replies Latest reply: Jun 3, 2015 7:48 AM by Sunny Talwar RSS

    null values showing as zero in pivot table

    Rhona Corcoran

      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

          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

            • Re: null values showing as zero in pivot table
              Rhona Corcoran

              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

                  Would you be able to share a sample?

                    • Re: null values showing as zero in pivot table
                      Rhona Corcoran

                      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

                          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

                            • Re: null values showing as zero in pivot table
                              Rhona Corcoran

                              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

                                  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