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

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

        • Re: Challenging : Max function aggregate problem
          Stefan Wühl

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

          )