Building XmR charts (Shewhart's control charts)

[Tomas2]

Hi all,

I’d like to build XmR charts using Looker, but have not yet succeeded in doing this. I’ve some time-series data from the first of January up to today, with new data being added on a daily basis. The dataset includes one value for my performance measure, per day. For the XmR chart I need to calculate the mean and standard deviation for a subset of the dataset, the control data (14 fixed, consecutive values of my performance measure in the dataset).

However, I’m not able to engineer table calculations such that it creates a measure which, for every date, computes the mean across the same time window. For example (see mock-up data), I’d like to calculate the mean and standard deviation for the period 2017-08-06 until 2017-08-19 and subsequently include this as a measure, where this value should remain constant (and the time window should stay fixed), also if new dates are added to the result set.

Anyone that has ideas how to solve this problem?

Thanks!