topic Re: AGGR function for absolute numbers in QlikView

AGGR function for absolute numbers

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: AGGR function for absolute numbers

sunny_talwar — Tue, 31 May 2016 18:30:51 GMT

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))

Re: AGGR function for absolute numbers

Wed, 01 Jun 2016 09:17:34 GMT

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.

Re: AGGR function for absolute numbers

sunny_talwar — Wed, 01 Jun 2016 11:14:10 GMT

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

Re: AGGR function for absolute numbers

Wed, 01 Jun 2016 13:15:19 GMT

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.

Re: AGGR function for absolute numbers

sunny_talwar — Wed, 01 Jun 2016 15:39:30 GMT

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)

Re: AGGR function for absolute numbers

Wed, 01 Jun 2016 15:55:47 GMT

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