AGGR function for absolute numbers

[#1]

Hi,

Below is an excel table which hopefully outlines what I am trying to achieve.  The absolute error figure has been calculated using the formula "fabs(Sum({$<Scenario = {Actual}>}Volume)/1000 -sum(Volume)/1000)" where the forecast is the selected variable.

In the column highlighted yellow, I want to calculate the absolute error total for each company - Company 2 should be 11.74(0.01+0.91+0.38+0.11+1.77+0.87+0.21+0.1+1.31+1.7+0.83+0.16+1.68+0.11+0.56+0.10+0.94). 

I have used the below formula in QlikView which calculates the number displayed below (i.e. 9.02):

=Sum(aggr(FABS(Sum({$<Scenario = {Actual}>}Volume)/1000-sum(Volume)/1000), CustomerName))

and have also tried

=Sum( aggr(Sum( FABS (Sum({$<Scenario = {Actual}>}Volume)/1000 -sum(Volume)/1000)), CustomerName))

which returned zero.

I think that the issue with the first formula is that it is adding up all of the numbers as positive and negatives and then making the total absolute (the total volume variance for Customer 2 is -9.02).  I want it to add the individual numbers as absolutes.

I am fairly new to QlikView and am sure that I have just ordered things incorrectly.  Any help would be greatly appreciated

[sunny_talwar]

I think you are missing the second dimension in your Aggr() function. Try this:

=Sum(TOTAL <CustomerName> Aggr(FABS(Sum({$<Scenario = {Actual}>}Volume)/1000-sum(Volume)/1000), CustomerName, PartDescription))

[#3]

Thank you so much for helping.  Unfortunately this is providing just one total number against each line.  I had tried adding the partdescription at the end of the AGGR statement (which did a similar thing) but hadn't tried the TOTAL bit.

[sunny_talwar]

Would you be able to share a sample? You have posted the above as an image, so won't be able to create a sample out of it. Please provide a qvw or Excel file and specify your expected output

[#5]

I have attached an excel file containing the information.  I am actually trying to calculate the average absolute error (column J) but was building this up in stages (hence needed to be able to sum the absolute values).  The output that I need is in column H.  Thanks you for continuing to help me with this.

[sunny_talwar]

Here you go:

Expression:

Volume Variance -> =Sum([Volume (Actual)]-[Volume (Forecast)])

Absolute Error -> =Sum(Fabs([Volume (Actual)]-[Volume (Forecast)]))

Sum Absolute Error -> =Sum(TOTAL <CustomerName> fabs([Volume (Actual)]-[Volume (Forecast)]))

Count Customer -> =Count(TOTAL <CustomerName> CustomerName)

Average Absolute Error -> =Sum(TOTAL <CustomerName> fabs([Volume (Actual)]-[Volume (Forecast)]))/Count(TOTAL <CustomerName> CustomerName)

[#7]

Thanks for that.  I am struggling to get my actual formula to work however I will look at it tomorrow with fresher eyes.