Hi Guys,
I am looking to create a Control Chart with Stages and have managed to make it match the calculation found in Stat software like MiniTab. My only problem is the way I have done it isn't dynamic and requires me to manual adjust each chart with a nested if for each. Example with 2 stages below for the UCL (Created as a Measure not a Reference Line):
If(Stage = 1,
Avg(Total {<Stage = {"1"}>} Volume) + 2.66*avg(Total {<Stage = {"1"}>} aggr(fabs(above(Volume)-[Volume]),[Date])),
Avg(Total {<Stage = {"2"}>} Volume) + 2.66*avg(Total {<Stage = {"2"}>} aggr(fabs(above(Volume)-[Volume]),[Date])))
I am explicitly needing to reference each individual stage in the measure to make the line split.
I have attempted the following to make it dynamically calculate based on the number of Stages present:
- Total <Stage> - an attempt to make it automatically Average per Stage on the Total Function. This does nothing.
- {<Stage = {"$(=Max(Stage))"}>} - This also only provides a single line and uses the Maximum stage to calculate.
- Looping in Data Load to create the forumla - This works but isn't as ideal as cracking it in a measure. It replicates the Nested If dynamically.
Any support would be greatly appreciated - even if is to just confirm that it is impossible!
Example Outcome attached.
Thanks
