×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Problem with Quarter To Date function

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.

3eyes

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 ?

sad  

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close