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?
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.
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.
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.
Thank you for the demo. When I created my chart, the bars were placed somewhat weirdly due to the grouping. Do you know if there is a way to place all bars in the center of each x-axis value?
