INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Aggregation function max problem

Aggregation function max problem

(OP)
Hi
 

My name is Jose Ignacio and I'm working for a software firm in Bilbao (Baque Country, Spain).
 

Actually I'm developing olap cubes in analisys services for a large institutional customer.

 
I forward this email to you cause I'm getting desperated with one problem I've not been able to solve yet. I thought it would be easy to solve but I'm afraid it's isnt that simple.

 

I made a cube that has  the following measures: Amount and Budget.
This measures are repeated for every rows of fact table.
 

Tha Budget makes reference to the Customer Code, "Division"
dimension,Posting Year and Posting Month.


I try to provide this data in this situation:


Measures.[Budget] (MAX aggregation mode)


      Filter Posting Year 2004
      Filter Posting Month genuary


       Budget
      Sell To Customer No Division Total
      BERG ? -
        DO ? 15.000,00
        PM ? 40.000,00
      BERG Total ? 40.000,00
      General Total ? 40.000,00


When I check the single Budget the result is correct.
But in the total it returns MAX of rows. I need the SUM. (55000) (DO+PM)

 
 What I'm trying to avoid with max aggregation function is to avoid sum amounts when there're repeated records.

Ex:

customer       Amount      Document

John              200         1

John              200         2

John              200         3

 

John should have the amount of 200. He has three documents with one single amount of 200.  The sum aggregation would result in 600 (200+200+200) for John and that is incorrect.

 

Max function applied on amount measure gives me the correct results when checking amount for each customer  but when I get the total result for 100 customers it always gives the max of all the rows instead of retrieving the sum of customer amounts.

 

How can I get the sum getting correct total amounts for each customer and for the grand total?


 

Thanks in advance

 

Best Regards,

 

Jose Ignacio.


 

RE: Aggregation function max problem

Add a additional measure that stores the Amount divided by the number of documents for each customer.

You will need to calculate this measure prior to building the cube (in the ETL process ?)

Customer          Amount      Doc      Amount2

John              150         1        50

John              150         2        50

John              150         3        50

Summing Amount2 yields 50+50+50 = 150
 

Ties Blom
 
 

RE: Aggregation function max problem

You can crete a document count measure, how you do this depends on the underlying engine, and then create a calculation which is amount / document count.

RE: Aggregation function max problem

if you are using SSAS as your OLAP engine you can specify your aggregation type as FirstChild or LastChild  not sure if this will fully fit in your situation.    It all depends if the base measure can and how it is rolled up through various levels.  You can probably do it with an MDX calculation that is pretty creative.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close