My makeover monday submission this week included a bar chart on a dual axis with two lines representing averages for the individual country as well as all of Africa. For countries with very few malaria deaths the temporal patterns were obscured by the Africa average line, as this forced the axis to go to at least 1,303.

 

 

I created a parameter that would give the user the option to exclude this average line in order to see trends for those countries with fewer deaths (IF [Show overall average] = ‘Yes’ THEN {AVG([Deaths])} END).

However, this means that if the user selects ‘No’ the values for this line will be set to Null. This creates a problem, as the indicator now shows up, informing us that we have 15 null values that cannot be plotted. Left-clicking the indicator gives us the option to either filter the data (which also filters the bars) or show it at default position (which moves the line to the zero mark). Neither of these options work in this case.

15 nulls

We have (at least) two solutions to get to our desired outcome.

  1.  If you right-click the indicator you will get the option to ‘Hide Indicator’. This will hide the warning indicator box but will not filter any of your data. You can also go to ‘Analysis’ > ‘Special Values’ and choose to either hide or show these.
  2. You can change your parameter calculation: IF [Show overall average] = ‘Yes’ THEN {AVG([Deaths]) ELSE -1 END. The line will now be plotted at -1, so if you set your axis to a fixed starting point of 0 and an automatic end point the line will be hidden when the parameter is set to ‘No’.

Now the user can use the parameter to either show or hide the average line for all of Africa, making it possible to compare the country’s average number of deaths to that of the whole continent but also to investigate the changes over time in countries that have only had very few deaths from malaria over the years.