This post is something of a “give the client what they have asked for and not what you think they wanted” vibe about it.
I had previously posted this a few months back and, as always, received some awesome help in solving it from the EDNA community.
The client had asked for a graph which breaks up the months in 8 month periods and takes an average of the defects during the previous 8 months and then uses this figure to set a base level with upper & lower limits (UPL & LPL). The 8 Month Period was overcome/created by Melissa adding in an additional column in her world beating Date Table called 8 Month Fixed Period.
In the example data given to me there is a 2.66 number used, I was “informed” this had some roots in Standard Deviation. So, I thought I would be clever and create the graphs using Standard Deviation giving the user the option to select 0 Standard D’s, 1 SD’s, 2SD’s or 3SD’s. (When I say I would create it…Brian J did the heavy lifting) Present it to the client and they want to use their original calculation as the 2.66 figure has a basis in a benchmark they’ve used in the past…so my bad & lesson learned.
Picture 1 is from the client and picture 2 is from the PBIX solution from EDNA. I need to recreate the UPL & LPL lines but using the client calculation (which is in the attached Excel document & displayed in Picture 3). In the Excel file, please ignore period 1 data and focus on the yellow highlighted Period 2 Data for understanding the calculations.
I’ve tried to recreate it using their logic but I can’t get it to work.
Hopefully I’ve laid out the problem clearly, if not feel free to throw some objects at me…as the client already has