Cell Alerts for Varying Targets

[Former Member]

Hi, can anyone help me figure out how to set up my dashboard so that I can use a variety of targets to determine whether the resulting display will be green, yellow or red? For example let's say I have 3 divisions in my complany and each division has multiple products (vaires from 3 or 4 to 8 to 10) and each product has it's own thresholds to determine whether it should be G, Y or R.  one product might have a threshold of 95% to be Green and 90% to be yellow while the next product in the same division might have a threshold of 90% to be G and 85% to be Y and so on.  This site is not letting me attache an exel file or a pdf to make it more clear so I've added some sample numbers below.  I'd like to represent these products in a column chart so that the columns will change color as well as the selector that I end up using (prob a lable component.)  Also, would I have to arrange all 3 divisions together in one block or can I separate them by 1 or 2 lines on my spreadsheet?  I've seen some discussion about using cell level alerts but I don't understand the logic behind this and it would be great to see an example of a cell level alert based on the data below also with a description of how to enter any formulas and what the numbers in the formulas mean.  Thank you for any assistance anyone can provide.

EX:                    Actual          Green               Yellow               Red

Division 1           98.88%          >=95%            90 to 95%          below

Product 2          100%             =100%             95 to 100%        below

Product 3          100%             >=98%             95 to 98%          below

Product 4           96.82%         >=93%             88 to 93%          below

Product 5           98.22%         >=95%             93 to 95%          below

Product 6           100%            =100%             98 to 100%        below

Product 7           82.82%         >=95%             93 to 95%          below

Product 8           90.73%         >-85%              75 to 85%          below

[arijit_das · Accepted solution]

Just got one idea using Stacked Column Chart.

Sample attached.

How to open the file:

1. Download the zip file.

2. Extract the file Sample.xlf.txt.

3. Rename it to Sample.xlf.

4. Open in Dashboards Designer tool.

Sample.xlf.txt.zip

[Former Member · Accepted solution]

I don't understand how the alert colors are being associated with the chart by way of the formulas you have in this.  Are you using some conditional formatting?  For example, can you explain how the IF statement for division 1 works?  I see where you have 3 different IF statements, one for green, one for yellow and one for red, but I don't get how that's turning the stacked chart columns different colors. Thanks again.

[arijit_das · Accepted solution]

Well, the idea is using a stacked column chart with 3 series - Red, Yellow and Green. Now, in those 3 columns, I have written if-else logic so that if the value falls in green range then I show the value in green and show blank in other columns.

For example, Division 1 has value 90% which falls under the yellow range (90%-95%). So, the value is populated by if-else logic in Yellow column and blank is shown in other 2 columns (because the value falls outside the range of green and red). This is how, always one of the 3 columns will have data and other 2 will have blank in a single row.

Now, I have applied 3 different colors to those 3 series - That's all

[Former Member · Accepted solution]

Awesome, thank you very much.  I have another question for you if you don't mind.  With some of my other charts that I'd like to create, some of the thresholds have small values (ex: <2% would be good) and others are based on 90% to 100%.  This makes it difficult to place them in the same chart since the smaller ones barely show up or don't show up at all compared to the bars in my graph that go up to 100%.  What do you recommend as the best way to display these in a chart where they are so different?  I've explored using a column chart and just showing one column at a time as well as using a vertical progress bar and different gauges (single value components).  Thank you once again.

[arijit_das · Accepted solution]

In this case, you can not compare the actual across different categories as the threshold value varies a lot. One with 2% can be good where another has to achieve 90% to be good.

So, better to have different charts like bullet chart/gauge for those categories as you have already tried, or, to use a single scorecard with alerter if number of category labels is not fixed.

Example: Say for Product 8, 2% means good, but Product 4 has to achieve 90% to be good.

An idea can be to have the column chart and scorecard both and control the visibility with a toggle button so that the user can see both chart and table as per their wish.

[Former Member · Accepted solution]

Hi there...I have another question for you if you don't mind.  In the image I've included below, my category labels are off centered from the columns above them.  I've tried a bunch of things to try to get them centered but so far nothing has worked.  Any suggestions?  Thank you.

[Former Member · Accepted solution]

[arijit_das · Accepted solution]

Instead of a normal column chart, try to use a stacked column chart.

[Former Member · Accepted solution]

The Stacked chart looks much better and everything is lining up propertly...thank you once again.

[arijit_das · Accepted solution]

Could you please close the thread if your issue is resolved ?

[arijit_das]

Not possible with a column chart. As an alternative, you can consider vertical bullet chart: