Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Jeffrey,
Check my attached file.
Regards,
Sokkorn
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
Sokkorn:
Thank you, too for the solution.
Regards
Jeff
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
Rob:
Thanks again. Makes sense. I also used the "rgb()" expression to get the red, green and yellow I wanted.
Jeff