How to calculate Cumulative and forecast

[New-Qlik]

Hi,

I have a two columns in table

1. Date

2. quantity

taking these two I have to calculate cumulative and forecast

1. Cumulative :

Example 1

cumulative wrt to 2016022601 = sum(quantity for 2016022601 + 2016022600)

cumulative wrt to 2016022602 = sum(cumulative value wrt previous date (2016022601) + quantity wrt to 2016022602)

2. Forecast:

Example1:. Forecast - 12 Hours on Actual wrt to 2016022605 =   average(2016022600,2016022601,2016022604,2016022605)*0.75+ (Forecast - 12 Hours on Actual of previous date(2016022604) *0.25)

Example2 . Forecast - 12 Hours on Actual wrt to 2016022606= average(2016022600,2016022601,2016022604,2016022605, 2016022606)*0.75+ (Forecast - 12 Hours on Actual of 2016022605 *0.25)

Row Labels	Sum of Qty	Forecast - 12 Hours on Actual	Cumulative Fcst Mvg Ave
2016022600	2
2016022601	3		5
2016022604	9		14
2016022605	21	7	35
2016022606	39	13	74
2016022607	6	13	80
2016022608	7	13	87
2016022609	24	13	111

Kindly let me know if there are any questions

Thanks
Avneet

[Anonymous · Accepted solution]

Hi

please check your calculation : is this right ?

2. Forecast:

Example1:. Forecast - 12 Hours on Actual wrt to 2016022605 =  average(2016022600,2016022601,2016022604,2016022605)*0.75+ (Forecast - 12 Hours on Actual of previous date(2016022604) *0.25)

Average : (2+3+9+21)/4=8.75

Average * 0.75 = 6.56

9*0.25 = 2.25

6.56+2.25 =8.86      ?

but you are showing as  7  ?

And try this in expression and let me know :

if(rowno()<4,'',((Rangeavg(above(Sum({1} Quantity),0,4)))*0.75)+((above(SUM(Quantity)))*0.25))

  = 8.81

1. Cumulative :

try this :

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

[Anonymous]

Hi

Have you checked my solution , if it is okay . please mark "correct anwer" or "helpful"

otherwise

some one else from community will surely help you......

[New-Qlik]

Hi Allu,

It is correct and working fine.Thanks for helping

Regards

[New-Qlik]

Can we build same expression in script level ?

Thanks

[New-Qlik]

How can we build this expressions in script.