Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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())))
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())))
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......
Hi Allu,
It is correct and working fine.Thanks for helping
Regards
Can we build same expression in script level ?
Thanks
How can we build this expressions in script.