Options to deal with missing and NULL representation in charts

[#1]

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?

[felipedl]

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?

[#3]

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

[felipedl]

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.

[#5]

Hi Felip,

I don't think I've managed to get across what I'm looking for, please find attached an example dataset and Qlikview document. I've exemplified my dataset and the problems I'm having with the three KPI_IDs.

When selecting KPI_ID 1; The graph shows months 1 through 5 and has a genuine dip to 0 in month 4.

When selecting KPI_ID 2;  The graph skips month 2 because the data is missing for that month and so can't be plotted. I would like, instead, for there to be a dot at month 1, then a gap showing month 2, and for the graph to continue with months 3 to 5.

When selecting KPI_ID 3; The graph shows months 1 to 5, but there is a NULL in month 3 which is being represented as a 0. This is incorrect because the value isn't 0 and I am looking for it to show a gap instead.

Using alt(value, 'string') changes the value for KPI_ID 3 from NULL to 'string' in month 3 but this has no impact on the chart presentation since the value is still being represented erroneously as 0 instead of a gap.

Thanks for your help so far! Hopefully there'll be an elegant solution somewhere.

Kind regards,

Tim Coultas

[felipedl · Accepted solution]

Hi Tim,

I understood what you mean now, see the attached example, now differentiating between Missing and Null values (I've put labels on them, so you can see the data in the table).

Basically what i did was join the CalTable dates with  all the different types of KPI_IDs, giving a complete month, KPI_ID table, and then I can say that the values that are incomplete on the first table (from Datatable) are "missing" and the joined elements of the months that aren't in this table are "null".

Felipe.

[#7]

Thanks Felip, yeah that'll do it!

Kind regards,

Tim Coultas