Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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…
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
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
Hi Allu,
Thanks for replying, but I am not able to open application ass you have created on personal edition.
Please guide.
Thanks
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 |
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 )
Thanks. Its working perfectly ..
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
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