1 Reply Latest reply: Jun 5, 2012 5:49 AM by Stefan Wühl

# Challenging : Max function aggregate problem

Hi guys, I got stuck in the following problem :

I have a table called "Fact Table" which has amounts per employee, Business Office, Business Area, etc.....

I'd like to do some internal calculations with the maximum amount per Business Office, Business Area, Employee, etc.... It depends on the case.

Here's my problem :

I have several business area, office and employees, and I only want to do a summation of the maximum Amount per Business Area.

If you do a summation of the Maximum Amount per Business Area, you will get 11.250.000 €.

 BUSINESS AREA TOTAL AMOUNT C. E. AND 883.333 C. E. BAR 2.166.667 C. E. BIL 256.667 C. E. GAL 770.000 C. E. GIJ 133.333 C. E. MAD 1 2.840.000 C. E. MAD 2 3.213.333 C. E. SAL 130.000 C. E. SER FINANC. 276.667 C. E. VAL 580.000 TOTAL 11.250.000

My problem is that I have 8 employees with the same Maximum Amount ( 2.840.000 €), and I only can get the maximum Amount once. So the other employees has a 0 value. As you can see:

11.250.000
... ... ... ...
... ...  ...  ...
... ... ...  ...
... ... ... ...

I'd like to have a table like the following one, with every employee who has a maximum amount, but internally counted once, because otherwise, the final result will not be 11.250.000 €.

11.250.000
... ... ... ...
... ...  ...  ...
... ... ...  ...
... ... ... ...

Do you have any idea about how to solve this??

Here I attach my little example in order to better understand me.

• ###### Re: Challenging : Max function aggregate problem

Maybe like attached?

Just use dimensionality() to distinguish between the total and your detail lines and then use NODISTINCT in your aggr():

if(dimensionality()=0,

NUM(sum(aggr( max({\$<  DIM1={'Risk Management Level 1'},DIM2={'Risk Management Level 2'},DIM3={'Risk Management Level 3'} >}AMOUNT), [BUSINESS AREA],DIM3)),'#.##0')

,NUM(sum(aggr(nodistinct max({\$<  DIM1={'Risk Management Level 1'},DIM2={'Risk Management Level 2'},DIM3={'Risk Management Level 3'} >}AMOUNT), [BUSINESS AREA],DIM3)),'#.##0')

)