December 8, 2009 at 1:53 am
Hello,
I'm using SSAS 2008.
I want to sum measures through each dimension except the Time Dimension.
For the time dimension, I need to extract the MAX.
First dimension : Customer Group - Customer Region - Customer
I want to sum the number of sales by Customer, by Customer Region and Customer Group.
However, I want to aggregate with MAX over the time.
If I choose the MAX Aggregate function, the MAX will be applied to each of my dimensions.
Any Workaround ?
Thank you for you next answer
Xavier.
December 13, 2009 at 9:11 am
Hi Xavier,
First take a look at this http://msdn.microsoft.com/en-us/library/aa902652(SQL.80).aspx
This article contains an expression to calculate the max values over time
Measures.[Maximum Inventory Value]:
Max(Descendants(Time.CurrentMember, Time.Month), Measures.[Value])
The behavior of the max function is explained here: http://www.databasejournal.com/features/mssql/article.php/3718391/MDX-Numeric-Functions-The-Max-Function.htm
Be aware the max function gives you the max ( default aggregation (measure) of the member ) in the set, opposed to the max aggregation function of the measure that takes the max values of the fact-table (read SQL server Records).
If your lowest grain of your time dimension is day, then with above function you get the accumulated sales for one particular day, summing all daily sales for one customer.
It all depends on the business question. If the question really is give me the maximum individual sales order amount for a customer in a given time period, then create a new measure max_individual sales.
So if you give the business question I am able to guide you to the right answer in more detail..
Regards Kees.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply