4 Replies Latest reply on Feb 28, 2020 5:31 AM by Sinisa Mikor

    Smart Reporting calculating percentages with null values

    Bill Jordan
      Share This:

      Hi,

       

      I have 2 columns of data in a Smart Report, each column comes from a separate sub-query.  I want to calculate the percentage of each value against the sum of the two, which works fine until there's a null value in one of the fields, then it fails to make any calculation for either of the two fields, including the one with an integer value. 

       

      I've tried using the Advanced Function feature to set the value from Null To Zero, a zero value shows in the column but the percentage calculation still fails, it returns 0% for both percentages.

      In this example col2 for John is null which has been replaced with zero using the Advanced Function.  The Sum Total on the report is correct, Johns percentages are incorrect, it should be 100% for %col1.

       

                          col1     col2     %col1     %col2

      Bob               4          1          80%     20%                

      John              1          0          0%        0%

      Total              5          1          83%     17%

       

      %col1 and %col2 are calculated fields, example: col1/(col1+col2*1.0). 

       

      I've also tried using a Case statement to change my calculation based on null values but anytime I insert the field that contains null values into the case statement it causes the report to fail with the error that it can't find the table.  We're on v18.08

       

      Any help is appreciated.

       

      Thanks,

      Bill

        • 1. Re: Smart Reporting calculating percentages with null values
          Sinisa Mikor

          Hi Bill,

           

          have you tried replacing col1 with ncol1 using something like

               CASE

                    WHEN

                         col1 IS NULL

                    THEN

                         0

                    ELSE

                         col1

                    END

          and col2 with ncol2 using

               CASE

                    WHEN

                         col2 IS NULL

                    THEN

                         0

                    ELSE

                         col2

                    END

          ?  That should replace NULL values with 0 in actual, rather than displayed, data so that calculations can be performed -- do remember to exclude undefined values in calculations with some constant value (in my example, 0)

               CASE

                    WHEN

                         ncol1 = 0 AND

                         ncol2 = 0

                    THEN

                         0

                    ELSE

                         ncol1 / (ncol1 + ncol2)

               END

          as well as

               CASE

                    WHEN

                         ncol1 = 0 AND

                         ncol2 = 0

                    THEN

                         0

                    ELSE

                         ncol2 / (ncol1 + ncol2)

               END

          1 of 1 people found this helpful
          • 2. Re: Smart Reporting calculating percentages with null values
            Sinisa Mikor

            I presumed values in col1 and col2 couldn't be negative -- if they can, use ncol1 + ncol2 = 0 instead of ncol1 = 0 AND ncol2 = 0 in percentage calculations.

            • 3. Re: Smart Reporting calculating percentages with null values
              Bill Jordan

              Hi Sinisa,

               

              I’ve tried that and the code here, using 0 and 1 and then summing instead of counting.  I would expect the value when I sum to be 0 but it’s blank, it’s also blank if I count.  If I go into Advanced Function and use the Null to Zero option a 0 will show up so it thinks it’s still null even though the case statement runs, the other filed processes correctly.

               

               

              CASE

               

                        WHEN

               

                             col1 IS NULL

               

                        THEN

               

                             0

               

                        ELSE

               

                             1

                        END

              • 4. Re: Smart Reporting calculating percentages with null values
                Sinisa Mikor

                You never mentioned which type of data was used for col1 and col2; I assumed they were numerical, but they could be textual which could change calculated fields a bit -- here's one method to achieve expected results without using advanced functions.

                 

                To begin with, define calculated field which indicates whether col1 is blank or not and an analogous calculated field for col2 -- both should automatically be declared as metrics and can be left out of report either by hiding columns or not using them as columns at all.

                     isblank1

                          CASE

                               WHEN

                                    col1 IS NULL

                               THEN

                                    0

                               ELSE

                                    1

                          END

                Define calculated field to hold value of col1 expressed as real number by multiplying it by 1.0 so that division used later to calculate percentage can return fractional quotients and an analogous one for col2. Again, they will automatically be declared as metrics unless you swap order of factors in multiplication in WHEN clause (col1 * isblank1 * 1.0) and thus implicitly declare them to be dimensions; if you wish to display last two columns as percentages rather than numbers ranging from 0.0 to 1.0 without changing formatting of columns, multiply by 100.0 instead of multiplying by 1.0.

                 

                ncol1

                     CASE

                          WHEN

                               isblank1 = 1

                          THEN

                               isblank1 * col1 * 1.0

                          ELSE

                               isblank1

                     END

                Finally, calculate percentage when possible, or replace it with zero when it isn't, using CASE statement below and an analogous one for percentage2; once more, these calculated fields will automatically be declared as metrics.

                percentage1

                     CASE

                          WHEN

                               ncol1 + ncol2 = 0.0

                          THEN

                               0.0

                          ELSE

                               ncol1 / (ncol1 + ncol2)

                     END

                1 of 1 people found this helpful