dimple - Plotting amounts as stacked bars for 52 weeks

Question

I'm trying to plot stacked amounts with dimple, for each week in a year.

• The x-axis needs to display week numbers, from 1 to 52.
• The y-axis displays stacked amounts coming from 3 tables that we want to plot as categories A,B,C.

The upper plot is almost what I want to achieve, but is missing weeks for which there are no amounts (e.g. weeks # 22 and 24), and all weeks from # 28 to # 52.

I added a "weeks" table, that lists all weeks from 1 to 52. An outer join is performed on the week number in order to format the x-axis, as visible on the second plot.

The PHP code that extracts the data for the upper chart looks like:

    function utf8ize($d) {
    if (is_array($d)) 
        foreach ($d as $k => $v) 
            $d[$k] = utf8ize($v);
     else 
        return utf8_encode($d);

    return $d;
}

$sql="SELECT s.num_week, category, amount_to_plot FROM weeks RIGHT OUTER JOIN
((SELECT WEEK(date,3) AS week,'A' AS category,SUM(amount) AS amount_to_plot FROM amounts_A GROUP BY WEEK(date,3))
UNION (SELECT WEEK(date,3) AS week,'B' AS category,SUM(amount) AS amount_to_plot FROM amounts_B GROUP BY WEEK(date,3))
UNION (SELECT WEEK(date,3) AS week,'C' AS category,SUM(amount) AS amount_to_plot FROM amounts_C GROUP BY WEEK(date,3)) 
 ) r
ON s.num_week = r.week WHERE s.num_week!=53 ORDER BY s.date_debut ASC
";

$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

$amounts = array();
while($row =mysqli_fetch_assoc($result))
  {
    $amounts[] = $row;
  }
$data = json_encode(utf8ize($amounts),JSON_UNESCAPED_UNICODE);
echo $data;

In the code above, by replacing, the "RIGHT OUTER JOIN" by a "LEFT OUTER JOIN", the json records for the missing weeks (i.e. those without any amount in A,B nor C) are generated as well and this is what we want. These look like:

{"num_week":"22","category":"","amount_to_plot":""}
{"num_week":"24","category":"","amount_to_plot":""}
{"num_week":"28","category":"","amount_to_plot":""}
(...)
{"num_week":"52","category":"","amount_to_plot":""}

However, there is a side effect and the graph looks totally absurd, plotting each amount in quantity of 1, including empty values.

I am not sure if this side effect might come from the fact that the "category" values are empty, as well as the amounts to plot ("" instead of "0.00").

My question is how can one patch this? I'm open to a diversity of methods: modifying the SQL query, the PHP code or dimple code.

Answer 1

There were two culprits, one at application level, the other in my PHP code.

At application level, the problem comes from the fact that dimple charting library doen't like the empty value for the amount to plot: ... {"num_week":"22","category":"","amount_to_plot":""} ...

Using a static json string, I could confirm that the problem was solved by adding "0.00" values: ... {"num_week":"22","category":"","amount_to_plot":"0.00"} ...

But the easiest solution was to adapt the PHP code. The issue was caused by the utf8ize() funtion in my PHP code, which replaced the null values by "0.00".

Instead of $data = json_encode(utf8ize($amounts),JSON_UNESCAPED_UNICODE);

simply use $data = json_encode($amounts); to pass the data to dimple.

Then the data will look like ...{"num_week":"22","category":null,"amount_to_plot":null} ...

If the json string is printed, directly after echoing it in PHP, it will look weird for accented characters (as utf-8 encoding will be used), but it will work perfectly as data feed for a dimple chart, also for its legend.

A further possible improvement woud be to replace "category":null by "category":<some-existing-category> in order to avoid an empty category in the legend.