How to calculate Average of 4 hours and cumulative

[New-Qlik]

Hi Everyone ,

I have to calculate average4 hour, average8 hour, Forecast - 12 Hours on Actual and cumulative .

I have two columns date and quantity.

Average of 4 hours will be : last four hour average , I need to display it wrt to time.

Please see attached excel for you reference.

Thanks In Advance.

Best Regards

Avneet

[#2]

Build an accumulation table to help your app understand how to accumulate the hours. The table will look like this:

Accum2hr:

HourOfTheDay	Accum2hour
1	2
2	2
2	3
3	3
3	4
4	4
4	5
5	5
5	6
6	6
6	7
7	7
7	8
8	8
Etc….	Etc ….

Accum4hr:

HourOfTheDay	Accum4hour
1	4
2	4
3	4
4	4
2	5
3	5
4	5
5	5
3	6
4	6
5	6
6	6
Etc….	Etc ….

Then join your data on the Hour column in your base data to the HourOfTheDay value in the table(s). What this does is tell your application that in the 2nd hour, the Accum2 values to accumulate are 1 & 2 and so forth. So you can now use the Accum<n> value(s) as a virtual dimension(s) to calc those values. Ie sum(<{Accum2Hour={[HourInYourDataField]}>  fieldtototal} – but be careful of potential data duplication if you use the accum<n>hour field in the dimensions of the chart.

You may also want to review the posts on “accumlations in pivot tables”.  Below is a code snippet I use to build a daily accumulation table

// Build the accumulation mapping table for the hours of a day
for vOuterLoop = 1 to 24
   for vInnerLoop = 1 to vOuterLoop
     HourAccumulation:
     LOAD
     $(vOuterLoop) as [AccumulatedHr],
     $(vInnerLoop) as [InvHr]
     AUTOGENERATE(1);   
   next
next

Hope this helps…

[New-Qlik]

Thanks Greg for replying. But my requirement is little different

I have below databse column names

Date	Quantity
2016022600	2
2016022601	3
2016022604	9
2016022605	21
2016022606	39
2016022607	6
2016022608	7
2016022609	24
2016022610	35

So average of 4 hours for Date = 2016022605 will be average of quantity for 2016022600, 2016022601, 2016022604, 2016022605 that means last four hours so  (2+3+9+21)/4 i.e 9

similarly for 2016022606 will be  average of 2016022601 +2016022604 +2016022605+ 2016022606 i.e (3+9+21+39)/4 = 18

so output should be like

Date	Quantity	Average 4 hours	Average 8 hours
2016022600	2
2016022601	3
2016022604	9
2016022605	21	9
2016022606	39	18
2016022607	6	19
2016022608	7	18
2016022609	24	19	14
2016022610	35	18	18

Kindly let me know if you have nay questions

Regards

Avneet

[Anonymous · Accepted solution]

Hi

In the editor:

LOAD Date,

    Quantity

FROM

[https://community.qlik.com/thread/224973]

(html, codepage is 1252, embedded labels, table is @3);

use following expressions

exp1:

sum(Quantity)

exp2:

for 4 hours average

if(rowno()<4,'',Rangeavg(above(Sum({1} Quantity),0,4)))

exp3:

for 8 hours avrage:

if(rowno()<8,'',Rangeavg(above(Sum({1} Quantity),0,8)))

:FYI , attached qvw as well......

Thanks

Allu

[New-Qlik]

Hi Allu,

Thanks for replying, but I am not able to open application ass you have created on personal edition.

Please guide.

Thanks

[New-Qlik]

I checked its working Allu.. Thanks

can u also suggest how to find cumulative

Row Labels	Sum of Qty	Average - 4 hours	Average - 8 hours	Cumulative Fcst Mvg Ave
2016022600	2
2016022601	3			5
2016022602	9			14
2016022603	21	9		35
2016022604	39	18		74
2016022605	6	19		80
2016022606	7	18		87
2016022607	24	19	14	111
2016022608	35	18	18	146
2016022609	52	30	24	198
2016022610	33	36	27	231
2016022611	32	38	29	263

[Anonymous]

Hi

Add one more exp 4:

for cumulative sums  (Cumulative Fcst Mvg Ave)

Rangesum(above(Sum( Quantity),0,rowno()))

Note : use all the expressions and dimensions in PIVOT TABLE (for better visualization )

[New-Qlik]

Thanks. Its working perfectly ..

[New-Qlik]

Hello Allu,

I have got one more requirement for calculation Forecast value  ie for

1. 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)

2. 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)

Kindly guide.

Row Labels	Sum of Qty	Average - 12 Hours	Forecast - 12 Hours on Actual	Forecast - 12 Hours on Average
2016022600	2
2016022601	3
2016022604	9
2016022605	21	9	7
2016022606	39	15	13
2016022607	6	13	13
2016022608	7	12	13
2016022609	24	14	13
2016022610	35	16	14
2016022611	52	20	16
2016022612	33	21	19
2016022613	32	22	20
2016022614	29	24	22
2016022615	88	31	24	12
2016022616	87	38	29	17
2016022617	128	47	36	19
2016022618	203	60	44	22

Thanks In Advance

Avneet

[New-Qlik]

Hi Allu,

I tried cumulative expression today it was displaying null because inorder to calculate cumulative value for first date it has to pick up data from quantity.

please see attached file.

Regards

Avneet