Metabase graph to show percentages is stacking values to exceed 100%

Hello,

I've been reviewing the documentation to MetaBase reporting, and it's very impressive. However, I haven't been able to figure out why the visualization of a percentage query, which shouldn't exceed 100%, shows values going well over that.

Here's an image of the graph:

This is the query:

SELECT left(from_unixtime(c.capture_time / 1000), 10) as date,
       a.name as 'system_name',
       b.name as 'device_name',
       c.read_count,
       c.valid_object_count,
       (read_count / valid_object_count * 100) as valid_read_rate
FROM sick_il_services.system_config a
         join sick_il_services.system_device b on a.id = b.system_id
         join sick_il_aap.daily_device_read_rate c on b.id = c.device_id
group by c.capture_time, a.name, b.name
having 1 = 1
[[AND c.capture_time >= unix_timestamp(concat({{start_date}}, ' 00:00:00.000')) * 1000 ]]
[[AND c.capture_time <= unix_timestamp(concat({{end_date}}, ' 23:59:59.999')) * 1000]]
[[AND a.name = '{{system_name}}']]
and valid_read_rate < {{expected_read_rate_percent}}

I noticed there was a warning message that says "date is an unaggregated field - if it has more than one value on a point in the x-axis, it's values will be summed". However, I've tried using the "capture_time" which is grouped, with the same result. Also tried using the the AVG function on the date field, but again, no luck.

How do I get this to show 100% as the max?

Also, is there a way to have each device to have a separate line in the graph, with maybe a separate color? That would be tremendously useful.

Thanks!

Hi @markd
The date is unaggregated, since you are grouping by c.capture_time instead of left(from_unixtime(c.capture_time / 1000), 10), which is aliased as date

To add more series, click visualization Settings > Data > under "X-axis" click the "Add a series breakout ..." button
But it's only possible to add 1 more breakout, and it looks like you have both system_name and device_name.
I don't know how you expect the breakout should behave - if you need to be able to see both, then you would use Optional Clauses and perhaps together with coalesce() - there's many hacks that can be done.

I cannot see which type of variable {{system_name}} is, but if it is Text, then it will automatically add correct quotation around the variable, so it should just be:
[[AND a.name = {{system_name}}]]

Hi @flamber,

I think the problem was that I had multiple devices for multiples dates. The first step was to limit the date to a single date in the selection (also a single system).

I then changed the x-axis to be ordinal, and to base it on device name instead of date, since that's closer to what I would like to replicate.

That is, read-rate is the y axis and, device-name is the x-axis, and a read-rate shows up as a a set of bars across the graph for each device.

Here is an example:

image

In the above image, the device ids are on the x-axis, the read-rate is on the y-axis, and the group of dark-blue or red bars going across the display are the read-rates for each device.

Right now, the visualization looks like this:

If you move the mouse up and down vertically over the long pink bar, it will pop-up a display which shows the read-rate and other info for a given device. That's very cool, but it would obviously be better if it could separate the devices into multiple bars similar to the first display. Note - I only will be using one system name and one date at a time.

Here's the query:

SELECT left(from_unixtime(c.capture_time / 1000), 10) as date,
       c.capture_time,
       a.name as 'system_name',
       b.name as 'device_name',
       b.id as device_id,
       c.read_count,
       c.valid_object_count,
       (read_count / valid_object_count * 100) as valid_read_rate
FROM sick_il_services.system_config a
         join sick_il_services.system_device b on a.id = b.system_id
         join sick_il_aap.daily_device_read_rate c on b.id = c.device_id
group by date, a.id, b.id
having 1 = 1
[[AND c.capture_time >= unix_timestamp(concat({{start_date}}, ' 00:00:00.000')) * 1000 ]]
[[AND c.capture_time <= unix_timestamp(concat({{end_date}}, ' 23:59:59.999')) * 1000]]
[[AND a.name = '{{system_name}}']]
and valid_read_rate < {{expected_read_rate_percent}}

Thanks for any thoughts you may have on this.

@markd I cannot see which fields you have placed in the axes - show the sidebar visualization settings.

@flamber

Sure thing. Here are the first 3, and I'll respond again with the next 2.

!

@markd I'm unsure what device_id is returning, but change Axes > X-axis scale from Histogram to Ordinal.
You would probably have to make another sub-select to do what you're after, since Metabase would need something like this:

device_id | category      | value
        1 | Read Count    | 2
        1 | Object Count  | 4
        1 | Valid Rate    | 0.5
        2 | Read Count    | 7
...

And then you would add both device_id and category series on the X-axis.