Maybe like this
INPUT:
LOAD ID,
Date#(Date,'MMM YYYY') as Date,
Sale,
Forecast,
Final
FROM
(ooxml, embedded labels, table is Sheet1);
LOAD *,
If(previous(ID) <> ID and Sale>0, 1, If(previous(ID) <> ID, 0, Rangesum(Peek(SaleCount), If(Sale>0,1)))) as SaleCount
RESIDENT INPUT
ORDER BY ID, Date desc;
DROP TABLE INPUT;
| ID | Avg({<SaleCount = {1,2,3}>}Sale) | Stdev({<SaleCount = {1,2,3}>}Sale) | upper reference | lower reference |
|---|
| | 5018 | 13395 | 18413 | -8376 |
| 1 | 31000 | 20298 | 51298 | 10702 |
| 2 | 40 | 23 | 63 | 17 |
| 3 | 15 | 13 | 28 | 3 |
| 4 | 261 | 274 | 535 | -14 |
| 5 | 200 | 252 | 452 | -52 |
