Conditional Count (with Color)

[jmonroe918]

I am creating a stacked bar chart that displays all open CAPA records by Division (the dimension). I also want each bar to reflect subsets of age ranges for the open records (<30, Between 30 & 90 and >90 days) showing as green, yellow and red,respectively.

I have a set analysis that filters out all of the "non-closed" records (Count({$<Status=Status-{'Closed'}>} [CAPA No])) as an expression, however I have not been able to figure out how to count the open records for each site based on each "Age (Days)" range so I can see for each division a bar stacked with green, yellow and red bands, as applicable.

Jeff

[rwunderlich · Accepted solution]

You can pre-calc the bucket in the script or create it as a Calculated Dim in your chart like this:

=if([Age (Days)] > 90, '>90'

,if([Age (Days)] > 30, '30-90'

,'<30'

))

I assigned the colors in the pallate order. If you want to get more specific you can use the background color attribute.

Also, I think your set modifier shoule be:

Status-={'Closed'}

See attached.

-Rob

http://masterssummit.com

[Sokkorn]

Hi Jeffrey,

Check my attached file.

Regards,

Sokkorn

[jmonroe918]

Rob:

Thanks for the solution. I tried to use the Background Color attribute with both statements you see below, but neither worked. I tried them in both the Dimension at the calculation and at the expression. Are they incorrect?

=if([Age (Days)] = '<30', green(),
if([Age (Days)] = '>90', red(),
yellow()
))

and

=if([Age (Days)] <30, green(),
if([Age (Days)]  >90, red(),
yellow()
))

Jeff

[jmonroe918]

Sokkorn:

Thank you, too for the solution.

Regards

Jeff

[rwunderlich]

Hi Jeff,

The Background Color attribute belongs in the expression. Because you are in an aggregation, you need to include an aggregation function like avg in the test:

=if(avg([Age (Days)]) > 90, red()

,if(avg([Age (Days)]) > 30, yellow()

,green()

))

-Rob

http://masterssummit.com

[jmonroe918]

Rob:

Thanks again. Makes sense. I also used the "rgb()" expression to get the red, green and yellow I wanted.

Jeff