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

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

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

## RE: Aggregation function max problem