1 Reply Latest reply on Dec 7, 2012 7:39 AM by Mark Holtz

    % of non-NULL values

    Ayush Baheti

      My data has a lot of measures (15) and a lot of NULL values. I need a simple  way to figure out how many values were entered and how many were null. In the example data set, [] represents NULL values.

       

      ID   Area   M1   M2   M3  M4  M5

      1       A      9     2     3     0       9

      1       A      9     2     []     0       9

      2       B      9     2     3     0       []

      2       B      9     []     3     0       9

       

      I do know a lenghty approach. Make a calculated field for each measure - COUNT([Measure]/COUNT(No. of Records)] and then make a graph with all of them as Measure values.

       

      I graph I need is something like this. Nothing complicated - just the percentage of non-NULL values to total values. Any quicker solutions?

       

      Tab.bmp

        • 1. Re: % of non-NULL values
          Mark Holtz

          The way your data is structured, I am not aware of any way to get around calculating whether each measure is null or not null.
          [M1 NonNull]:
          IF NOT(ISNULL([M1])) THEN 1 ELSE 0 END

          [M2 NonNull]:
          IF NOT(ISNULL([M2])) THEN 1 ELSE 0 END
          ...

          [M15 NonNull]:
          IF NOT(ISNULL([M15])) THEN 1 ELSE 0 END

           

          Then you should be able to make your percent as SUM([NonNull Measures]) / SUM(Number of Records) as a calculated field to include on the view.

           

          If you changed the structure of your data so that each unique dimension combination AND measure had its own row, (I call this "unpivoted data") then I believe you could get to it without having to create a field for each separate measure.

          You'd simply create a single calculated field of IF NOT(ISNULL([M1])) THEN 1 ELSE 0 END and put your [Measure] on the columns shelf to split the view.

           

          IDAreaMeasureValue
          1AM19
          1AM22
          1AM33
          1AM40
          1AM59
          2BM19
          2BM22
          2BM33
          2BM40
          2BM5[null]
          1 of 1 people found this helpful