INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Problem with Quarter To Date function

Problem with Quarter To Date function

(OP)
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

RE: Problem with Quarter To Date function

(OP)
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 ?

RE: Problem with Quarter To Date function

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

Oh no!  Help anyone ?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!