How to display zero values in the legend of PlotALot graph based on RSForm Pro results?

Question

In the backend, I am compiling some survey results generated from RSForm Pro and I want to display the statistics graphically using a PlotALot pie chart.

My form has 4 possible values for this particular question: Australia, NewZealand, Oceania, and Other.

I started with the following query in the sql textarea of the Plot1 tab:

SELECT FieldValue, COUNT(1)
FROM jprefix_rsform_submission_values
WHERE FormId = 8
  AND FieldName = 'Location'
GROUP BY 1
ORDER BY 2 DESC

However, this omits any location that has a count of zero. To ensure that all possible locations were included, I derived a table of default locations via a subquery, then joined the RSForm data to the derived table.

SELECT 
    Location,
    COUNT(FieldValue)
FROM (
    (SELECT 'Australia' AS Location)
    UNION
    (SELECT 'New Zealand')
    UNION
    (SELECT 'Oceania')
    UNION
    (SELECT 'Other')) AS defaults
LEFT JOIN jprefix_rsform_submission_values
    ON FormId = 8
       AND FieldName = 'Location'
       AND FieldValue = REPLACE(Location, ' ', '')
GROUP BY 1
ORDER BY 2 DESC

This now provides the correct result set:

Location    |  COUNT(FieldValue)
--------------------------------
Australia   |       46
Other       |        7
New Zealand |        0
Oceania     |        0

The trouble is, when I feed this sql to PlotAlot, I only see:

How can I prevent PlotAlot from omitting the zero values in the legend?

Answer 1

I managed to solve this by amending the second column in the SELECT clause to generate a minuscule, non-zero decimal value.

IF(COUNT(FieldValue) > 0, COUNT(FieldValue), 0.1)

For the record, GREATEST(COUNT(FieldValue), 0.1) will have virtually the same result set (technically, all of the results become float values) and the graphic output is the same.

As a result, the Locations appear in the legend and the 0.1 values are rounded to 0.

---

As a potentially related issue dealing with small values being lumped together into an "Other" label in the legend, it might be solvable by setting the sliceVisibilityThreshold value to 0.

To implement this, go to PlotALot's chart editor, click the Extra tab and inside of the Extra Options field, enter sliceVisibilityThreshold: 0.