July 17, 2008 at 3:10 pm
I have a simple fact table that doubles as a dim table (date, quarter form a hierarchy in the time dimension)
ID price activity date quarter
-----------------------------------------------------------------------------------
1100 10 1/1/2007 12:00:00 AM Q1
290 25 2/1/2007 12:00:00 AM Q1
381 32 3/1/2007 12:00:00 AM Q1
....................................................................................................................
....................................................................................................................
12 40 20 12/1/2008 12:00:00 AM Q4
To keep this simple, I have 12 records in the table one for each month of the year 2007 (four quarters).
I want to calculate a weighted average of the price over the 12-month period based on quarter.
For quarter 1, Q1, for example, that would be (based on the values above):
(100*10 + 90*25 + 81*32)/3.
Here, I have summed over the price*activity products for Q1 and divided by 3 (no. months per quarter).
I need to come up with an MDX expression for a calculated member that does this on the fly per quarter.
Here is my 1st clumsy attempt at this:
Avg([Fact Price Activity].[Quarter],
[Measures].[Activity]*[Measures].[Price]
)
Well, it didn't work...
Anyone know how to do this?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 18, 2008 at 2:49 pm
Well, I have finally been able to come up with the right MDX query, but, although it works in SSMS, it gives me an error in the BIDS project (calculated member):
MdxScript(Price) (9, 5) Parser: The syntax for 'WITH' is incorrect.
Anyone have any ideas why this is happening? 🙁
Here is the query (again it works fine is SSMS!):
WITH
MEMBER [Measures].[wavg] AS
'SUM( DESCENDANTS( [Fact Price Activity].[Hierarchy].CurrentMember, [Fact Price Activity].[Hierarchy].[Month]),
[Measures].[Price]*[Measures].[Activity])
/
COUNT( DESCENDANTS ( [Fact Price Activity].[Hierarchy].CurrentMember, [Fact Price Activity].[Hierarchy].[Month] ) )'
SELECT
[Fact Price Activity].[Hierarchy].[Quarter].MEMBERS ON columns
FROM
[Price]
WHERE
[Measures].[wavg];
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 19, 2012 at 12:13 pm
...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply