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!

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

Jobs

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!

Resources

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