topic Total Average Forecast Value in QlikView

Total Average Forecast Value

Tue, 17 Apr 2012 06:15:12 GMT

Below I have a table with actual calls and forecasted calls (and the script below to calculate it).

In the example, I need to use the value of 8031 (total of forecast calls) in an expression similar to this one:

IF(TOTAL_FORECAST_CALLS > 5000, FORECAST_CALLS x 10)

Where TOTAL_FORECAST_CALLS = 8031 and FORECAST_CALLS = 259

Day	Actual Calls	Forecast Calls
Total	4145	8,031
1	285	259
2	305	259
3	195	259
4	265	259
5	300	259
6	145	259
7	125	259
8	390	259
9	45	259
10	455	259
11	360	259
12	380	259
13	365	259
14	335	259
15	195	259
16	0	259
17	0	259
18	0	259
19	0	259
20	0	259
21	0	259
22	0	259
23	0	259
24	0	259
25	0	259
26	0	259
27	0	259
28	0	259
29	0	259
30	0	259
31	0	259

Total Average Forecast Value

Tue, 17 Apr 2012 06:15:58 GMT

=avg( TOTAL Aggr(if(DAYNUMBER <= day(Today()), sum(IF(YEAR = $(vCurrentYear) AND MONTHNUMBER = $(vCurrentMonth)AND LEDGER = 'SOPCALC' ,VOLUME))),DAYNUMBER))

Total Average Forecast Value

Thu, 19 Apr 2012 10:24:38 GMT

I managed to resolve this using the below script:

Instead of the above forecast script i used:

sum

(total(if(DAYNUMBER<= Day(Today()),IF(YEAR = $(vCurrentYear) AND MONTHNUMBER = $(vCurrentMonth)
AND LEDGER = 'SOPCALC',VOLUME))))/ num(Day(Today()))

and then used this script to return the total of that:

sum

(total(if(DAYNUMBER<= Day(Today()), IF(YEAR = $(vCurrentYear) AND MONTHNUMBER = $(vCurrentMonth)
AND LEDGER = 'SOPCALC',VOLUME))))/ num(Day(Today()))* max(total(DAYNUMBER))