Basic SAS Question on aggregation

Hello, I'm running SAS on SQL Server 05 Standard edition.

I noticed the 'Semi-Additive' features are not available in SQL Std Edition.

I have some time phased cost data that I don't know how to handle using only the 'Sum' feature

For example:

Week 1: Expenses =100 (based on sum of all expenses in the fact table)

Week 2: Expenses = 80 (ditto)

Week 3: Expenses = 0 (no records in fact table for this week)

Week 4: Expenses = 0 (ditto)

Monthly Total: 180

Now, I want to have a column that is a running total of expenses. Like so:

Week 1: Total Spent = 100

Week 2: Total Spent = 180

Week 3: Total Spent = 180

Week 4: Total Spent = 180

Monthly total = 180

Any clue how to approach this?

Hello. You will need to create a calculated member for this and write some MDX like:

SUM(YTD([TimeDimension].[TimeWeekHierarchy].CurrentMember), [Measure].[Total Spent]))

It will give you accumulated values.

HTH

Thomas Ivarsson

|||It would be better if you use AGGREGATE instead of SUM|||

Create Member CurrentCube.[Measures].[Total Spent]
as
SUM(YTD([time].[week].CurrentMember), [Measure].[Total Spent]))

this give me a syntax error... sorry I'm a MDX newbie.

|||You have set up a recursive calculation here, creating [Total Spent] which attempts to sum itself (resulting in an infinite loop). I suspect that you meant to use a different measure inside the sum() function.|||yeah...I did.|||

My mistake. I should have written Expenses for the Measure in the calculated member([Measure].[Expenses]) .

Regards

Thomas Ivarsson