Search code examples
averageaggregationbusiness-objectswebi

Manifold AVG aggregation in Webi 4.2

I am trying to produce a report in SAP BusinessObjects Webi 4.2 that has complex aggregation, however not having much luck.

I have data as follows:

Source Data

I am trying to create a variable that returns the average count of skills for Colleagues in each Store and Region, for example:

Required Output 1

And...

Required Output 2

I've tried =AVERAGE(([Skills]) in ([Colleague])) however, this seems to return the SUM rather than the average.

Apologies if I'm missing something simple... it's been a long day... and any help will be GREATLY appreciated!

Solution

  • Embrace the sum:

    =Sum([Skills]) / Count([Colleague];All)

    Updated for comment If [Skills] is a variable, then it gets a little more complicated. This worked for me with a copy of your sample data:

    =Sum([Skills] ForEach ([Store];[Colleague]) ) 
 / Count(1 In ([Region];[Store];[Colleague]);All)

    Note that if you have other lower-level dimensions in the block, then you will need to add them to both the ForEach and Count functions.

    I got a slightly different result that you for East Anglia (3.22), but based on my manual calculation, 3.22 is correct.