Problem with Quarter To Date function

[Nassy]

Hi

I am trying to return a query that will return just one cell showing the Quarter to Date revenue for the current quarter.

I have defined a QTD Revenue calculated member and am then trying to return its value by referring to it in a subsequent MDX select query. Exact code used is shown below:


WITH
MEMBER [Measures].[QTD - Revenue] AS
iif([Date].[Fiscal Calendar].Currentmember.Level.Ordinal = 0,([Measures].[Revenue]),
SUM(QTD([Date].[Fiscal Calendar].Quarter.Level),[Measures].[Revenue]))
SELECT
{[Measures].[QTD - Revenue]} ON COLUMNS
FROM [Company Sales Cube]


Hopefully it will be apparent that I am trying to create a calculated member for QTD Revenue that sums the Revenue at the quarter level using the syntax:
SUM( QTD (Quarter Level, Measure))

The hierachy of the date dimension has a standard day - month - quarter - year structure.
However the query when run is returning a massive figure and that is because it must be summing up Quarter to date from the very first quarter that exists within the cube up to today's date.

A Calculated Measure [YTD - Revenue] has already been written by someone else that appears to return the correct result when selected providing I add the current year [08/09] in the where clause.


I am very new to MDX and I am a bit stuck as to how to return just the Current Quarter's Quarter to Date figure.

If it helps the cube already has a named set defined called Current Quarter but I am not sure how to use it to filter my query result set or even if this is possible.

Any ideas on this? Is there may be a better way I can return in MDX just one cell value that is equal to revenue from the start of the quarter.



Nassy

 

[Nassy]

Ok I think I may have resolved this myself... I have come up with this query to return a Quarter to Date measure.


WITH

MEMBER [Measures].[QTD - Days - Worked] AS
'Sum(PeriodsToDate([Date].[Fiscal Calendar].[Fiscal Quarter]),[Measures].[Days - Worked])'

SELECT

([Measures].[QTD - Days - Worked],[Current Quarter]) ON COLUMNS
FROM [Wallchart Analysis]


If I have done some utterly ridiculous and you think this will return the wrong value then please let me know -
or if anyone has any tips/ good practise ?

 

[Nassy]

Actually doesn't appear to be giving me the right results - appears to be summing future dates as well..

Oh no! Help anyone ?