Graph with reference line and conditional formatting of bars

Question & Answer
, , ,
1

Hi,

I’m looking to create a bar chart with a reference line, with a signal colour (conditional formatting) for the bars when the target is not met.

First, the reference line. Every period, targets are adjusted. I’d like the reference line to show this adjustment. However I can’t seem to understand how to use the “Aggregated as” and “Calculate” to achieve this result. I’m only able to select a single value and it shows a straight line (which it shouldn’t) whatever I choose.
I prefer to use the reference line instead of the line from the combo chart since the customer demands the reference line to be dashed and I have not been able to create a dashed line in the combo chart.

Second, the conditional formatting. The client demands the colours of bars where the target has not been met to change colour. Unfortunately conditional formatting is not possible.
I have tried to create a calculated field which changes from 0 to 1 when the actual is smaller than target. However, when adding it to the group/colour field I now recieve the following message: “Custom aggregation field is not allowed as a dimension”.

How can I achieve the result demanded by the client?

2

Please refer below link for the conditional formatting of Bars.
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Visual-Bar-Conditional-Formatting

As far as the reference line is concerned, line from combo chart will be the option if the target changes for each period. I can add a feature request to have dashed line in combo charts.

3

Hi, @LinkeLoutje. Did Vetri’s solution work for you? I am marking her reply as, “Solution,” but let us know if this is not resolved. Thanks!

4

Hi @n_vetri,

That feature request would be great!

Regarding your suggestion, I have tried to implement something similar as the example, however this results in the mentioned error message: “Custom aggregation field is not allowed as a dimension”.

Maybe you can help a little when I explain what I’ve tried. The following calculated fields are created:
KPI1 Bar Colour = ifelse({KPI}<MAX({Target}), ‘coloured’, ‘neutral’)
{KPI} = SUM({numerator_field})/SUM({denominator_field})

In the visual I use the date on the x axis, and {KPI} for the bars.

Why is it still throwing the error?

Thanks very much for your help

5

WORKAROUND for BAR
Split up the colors with ifelse calculated field. Basically you would have 3 calculated fields: Red, Yellow and Green.
then add these fields to the visualization and select stacked bar chart. Hide the legends.

ex: on a scale of 0 to 100. 0 to 40 is RED, 41 to 70 is Yellow and 71 to 100 is Green then

calculated field Red: ifelse ( {column_name} <= 40, {column_name} , 0)
calculated field Yellow: ifelse ( {column_name} > 40 and {column_name} <=70, {column_name} , 0)
calculated field Green: ifelse ( {column_name} > 70, {column_name} , 0)

add these 3 fields to y axis and dimension to x axis. from the visualize menu, select stacked bar. you would be able to edit the colors.

1 Like