6 Replies Latest reply: Oct 5, 2017 4:44 AM by Tim Coultas RSS

    Options to deal with missing and NULL representation in charts

    Tim Coultas

      Hi Qlik Community,


      I've been scouring the forums attempting to gather all the best ways to handle nulls and missing values in charts.


      My SPC chart plots KPIs across the months in financial years, pretty standard.

      When the value of the data in the month is 0, I need to show that because it's significant.

      When the value of the data in the month is missing, I also need to show that because the submission should have come in but didn't.


      Currently, if the data is missing in the month then the chart will skip that month.

      1. I could remedy this with "show all values" on the month dimension. This isn't desirable because then the dimension will no longer react to selections and the data point value will show as 0, which is not correct.
      2. I could check "suppress zero-values" under presentation to solve this issue but then genuine 0s will be suppressed, which is not correct.


      My current method for solving this issue is to create a Cartesian product of the fact table with the master calendar on the primary key(s) so that I have rows of NULLS ready to be filled with data, leaving NULLS where the data is missing. This method has its own downsides, namely the fact that it's a really big table with nothing in and in order to distinguish between missing and 0 values, you need a system that reports positively on 0 values.


      I'm sure the Qlik Community has come across this problem before, what are your solutions?

        • Re: Options to deal with missing and NULL representation in charts
          Felip Drechsler

          Hi Tim,

           

          How would you differentiate the missing and null values?

          Is there a flag or something that says that for a specific month, the value is missing rather than null?

          As im thinking it, either way the value is "missing" not being quite clear as to what makes the difference between the two.

          How would you plot something if its missing and has zero value as to not having a value for the month and so being null?

            • Re: Options to deal with missing and NULL representation in charts
              Tim Coultas

              Hi Felip,

               

              For my purposes it's not necessary to distinguish between missing and NULL values, only between 0s and missing or NULL values. Apologies, I should have made that clearer.

              There is not currently a flag but the distinguishing feature between the two would be that NULLS have empty rows in the data and have dates attached, while missing values are not in the dataset at all. Unless I create my Cartesian product I would not expect NULLS in the dataset, only missing data.

               

              If the data is missing or NULL I would expect the line on the chart to stop for that month, then continue when data is available again, but I would expect the empty month to be visible so the user can identify that there is no data. I would also expect the line to dip down to 0 when the value of the KPI is 0 for that month. Does that make my meaning clearer?

               

              Kind regards,

              Tim Coultas

                • Re: Options to deal with missing and NULL representation in charts
                  Felip Drechsler

                  Hi Tim,

                   

                  I would do something like this on the field your trying to plot in the KPIs, by using the Alt(Val1,Val2) statement.

                  This way, if the row exists and the value isn't there, it's missing, and so its filled with a zero (or other value that you want) rather than not showing it, because there's no data to do it.

                   

                  TableX:

                  Load

                       *, // fields that are in the table

                       Alt(Value,0) as Value // in this case, if the value is missing, it will be zero, rather than null

                  From [Whatever];

                   

                  As i'm seeing it, when you select a month that has incomplete data (missing) the Value field in the example would be 0, showing in the KPI.

                   

                  Hope it helps.

                   

                  Felipe.