Forecast Accuracy Help

[#1]

I've been stumped on this forecast accuracy report for some time now. I'm trying to take the Absolute value of Forecast less Shipments divided by Forecast. I'm using the following formula:

sum(AGGR(sum(fabs(Fcst-Ship)),Item))

As far as I can tell, this is the formula I need to use so that the total will sum all of the item's ABS. The formula works fine so long as I am load a value for the forecast and a value for the shipment. However, if an item has been shipped but had no forecast (and therefore no item loaded into the table for forecast) the absolute value cannot calculate.

This is the table I get in QlikView:

Item	Fcst	Ship	ABS	ABS %
Total	1865	1425	310	16.62%
Item1	340	500	160	47.06%
Item2	725	575	150	20.69%
Item3	800	0	0	0.00%
Item4	0	350	0	-

The total ABS should be 1460 and the ABS % should be 78.28% (ABS Total of 1460 divided by Fcst Total of 1865).

Anyone have an idea of what I can do?

Thanks,

Justin

[#2]

Hello Justin,

take a look at my straight table of attached app.

Note that between the brackets are [column-names] of the chart, not fields.

HtH

Roland

[#3]

Sorry, Roland. I made a mistake in my original post.

The total ABS% should be 78.28%, which is the sum of the ABS column divided by the sum of the Forecast column. The 23.59% is the percentage.

I still haven't been able to figure out a formula to get the Total ABS column to be the absolute variances without have to click "Sum of Rows."

[#4]

Hi Justin,

an expression is valid for the whole column (same calc. for all "cells" of that column). So try this expression in an add. column:

=[ABS] / sum(TOTAL Fcst)

with %-format and also check "Sum of rows"

HtH

Roland

[#5]

Are you able to find the solution for this question?