4 Replies Latest reply: Oct 14, 2014 10:29 PM by Purna Cheekati

# Forecast Accuracy Help

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:

ItemFcstShipABSABS %
Total1865142531016.62%
Item134050016047.06%
Item272557515020.69%
Item3800000.00%
Item403500-

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

• ###### Re: Forecast Accuracy Help

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

• ###### Forecast Accuracy Help

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."

• ###### Re: Forecast Accuracy Help

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

• ###### Re: Forecast Accuracy Help

Are you able to find the solution for this question?