1

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!

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
D10N-CB3
  • 43
  • 8

1 Answers1

2

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.

Joe
  • 6,767
  • 1
  • 16
  • 29
  • Thanks... this isn't quite doing what I am expecting though... I think it may be because [Skills] is actually a variable with =count([SkillGroups]). I think the above is returning the total SkillGroups that all colleagues are members of, divided by the total of colleagues. – D10N-CB3 Feb 24 '20 at 10:37