Reference Lines

[#1]

Hi Qlik heads,

Thank you.

John

[swuehl]

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