Actual and Forecast

[New-Qlik]

Hellow everyone

I have a requirement in which we need to calculate actual cumulative value and forecast

Actual is coming wrt to quantity available ie for cumulative(2016072501) = sum of quality for (2016072500 + 2016072501)

                                                                                              cumulative (2016072502)= cum(2016072501) + quantity(20160725012)

Now I want that if quantity is null then calculate cumulative like cumu(2016072511) = cum(2016072510) + trend(2016072511)

this will be forecast

example

Date	Quantity	Cumulative	Trend
2016072500	1
2016072501	2	3	10
2016072502	3	6	10
2016072503	4	10	10
2016072504	5	15	10
2016072505	6	21	10
2016072506	7	28	10
2016072507	8	36	10
2016072508	9	45	10
2016072509	10	55	10
2016072510	11	66	10
2016072511		76	10
2016072512		87	11
2016072513		99	12
2016072514		112	13
2016072515		126	14
2016072516		141	15
2016072517		157	16
2016072518		168	11

[New-Qlik]

Cumulative I can calculate by using this

if(rowno()=1,'',Rangesum(above(Sum( Quantity),0,rowno())))

[jonathandienst]

In the load script, like this:

LOAD

  Quantity,

  Trend,

  Alt(Quantity, Trend) + Alt(Peek(Cumulative), 0) as Cumulative

FROM ...

Alt function returns the first valid numeric from its list of parameters. Similar to SQL COALESCE function (but not the same).

[New-Qlik]

Thanks Jonathan .

My source hass just two columns i.e. date and quanltity.

I have writing cumulative expression in chart itself

if(rowno()=1,'',Rangesum(above(Sum( Quantity),0,rowno())))

is there a way I can write it into script and then I can use your expression

Alt(Quantity, Trend) + Alt(Peek(Cumulative), 0) as Cumulative

Thanks

[jonathandienst]

So you want to calculate the trend value? What sort of trend curve would you like?