SPC average for sum data

[infock12]

Hi all,

I am trying to create an SPC chart where I have to calculate a percentage with a numerator and denominator. I am unable to work out what would be the best expression to make it work. The simple basic expression is below:

sum(Numerator)/sum(Denominator) - I've created a variable with this expression called vPercentage )

Tried average line with =avg(aggr(sum([Numerator])/sum(Denominator), [Month Year])) which seems to work

Tried a few expressions to create UCL and LCL with the below but it does not work.

vAvgLine + Stdev(aggr(Avg(vPercentage), [Month Year])) *vStdDev

I've attached the files. Any help is appreciated.

Thanks

[lironbaram · Accepted solution]

hi 

have a look the attachment 

your problem is that your stdev part returns null because it try to calculate it for each month alone 

you need to calculate it the same way you calculate the avg in a variable 

[infock12]

This is fantastic. Thank you very much Liron!

[lironbaram]

great, i am happy it helped you