Challenging : Max function aggregate problem

[marcel_olmo]

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:

BUSINESS AREA	BUSINESS OFFICE	EMPLOYEE	AGGR AMOUNT
			11.250.000
...	...	...	...
...	...	...	...
C. E. MAD 1	COMPANIES MAD 1	PED 071195	2.840.000
C. E. MAD 1	COMPANIES MAD 1	COL 075967	0
C. E. MAD 1	COMPANIES MAD 1	GEN 048801	0
C. E. MAD 1	COMPANIES MAD 1	JAC 076204	0
C. E. MAD 1	COMPANIES MAD 1	JOS 076110	0
C. E. MAD 1	COMPANIES MAD 1	MAR 070452	0
C. E. MAD 1	COMPANIES MAD 1	MIG 070647	0
C. E. MAD 1	COMPANIES MAD 1	NIE 066694	0
...	...	...	...
...	...	...	...

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 €.

BUSINESS AREA	BUSINESS OFFICE	EMPLOYEE	AGGR AMOUNT
			11.250.000
...	...	...	...
...	...	...	...
C. E. MAD 1	COMPANIES MAD 1	PED 071195	2.840.000
C. E. MAD 1	COMPANIES MAD 1	COL 075967	2.840.000
C. E. MAD 1	COMPANIES MAD 1	GEN 048801	2.840.000
C. E. MAD 1	COMPANIES MAD 1	JAC 076204	2.840.000
C. E. MAD 1	COMPANIES MAD 1	JOS 076110	2.840.000
C. E. MAD 1	COMPANIES MAD 1	MAR 070452	2.840.000
C. E. MAD 1	COMPANIES MAD 1	MIG 070647	2.840.000
C. E. MAD 1	COMPANIES MAD 1	NIE 066694	2.840.000
...	...	...	...
...	...	...	...

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

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

Many thanks in advance !!!

[swuehl · Accepted solution]

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

)