Bar Chart Conditional Color Formatting

Question

question

Mohamed Eldakroury asked Apr 1, '24 Mohamed Eldakroury commented Apr 1, '24

Bar Chart Conditional Color Formatting

Hello,

What is the best way to conditionally color bars in bar chart? There is another similar question on this forum (https://answers.flexsim.com/questions/116572/dashboard-conditional-color.html) and the recommended answer was to add "color" as a column in a statistics collector and use that as a variable to control color. However, I am pulling data from a calculated table and couldn't use the if statement in the calculated table, and I also want to do more than 2 colors depending on the value, let's say 0-10 GREEN, 10-20 BLUE, 20 and above RED.

Here's a sample model. I want to change the color of the bars for all 4 zones depending on the average weight value from the "AverageWeight" Calculated table.

BarChartConditionalFormatting.fsm

Software Version:

FlexSim 23.2.0

calculated table color change formatting

barchartconditionalformatting.fsm (41.9 KiB)

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2024-04-01T16:24:59Z

Jordan Johnson answered Apr 1, '24 Mohamed Eldakroury commented Apr 1, '24

This is a little roundabout, but it seems to work:

barchartconditionalformatting_1.fsm

The basic problem you faced is not being able to use if statements in your query. Technically, it is possible to use "CASE WHEN" statements. But in your case, I think that would be a lot of complicated SQL.

The approach I used in this model is a user command. FlexQL (FlexSim's SQL engine) recognizes most FlexScript commands as SQL functions. In this case, I made a function that returns a "rating" word based on a number, in a command called "ratingFromValue" that accepts a single number and returns a string:

double value = param(1);

if (value <= 10) {
  return "Good";
}

if (value <= 20) {
  return "Acceptable";
}

return "Overflow";

You can see that it would be easy to add in more color boundaries in this code.

Then, in the Query Builder, I added a new column:

Then in the chart, I colored by the Rating column:

1711988631201.png (17.7 KiB)

1711988653460.png (24.8 KiB)

1711988690746.png (24.5 KiB)

barchartconditionalformatting-1.fsm (108.6 KiB)

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Mohamed Eldakroury commented · Apr 01 at 05:36 PM

Hi Jordan,

That worked, thank you!

I was going to go the SQL route and started looking up some SQL code but I thought there must be something simpler.

0 ·

question