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.

Students Click Here

QTD/YTD Metrics!!!!

QTD/YTD Metrics!!!!

QTD/YTD Metrics!!!!

need help on how to do Quarter Till Date (QTD), Year Till Date Upto Current Month (YTDCurMonth), Year(Y) metrics....

any help will be highly appreciated...


RE: QTD/YTD Metrics!!!!

Are you using transformation tables, or will you be doing the transformations through MicroStrategy?  Look in the knowledge base for more information on Transformations.

RE: QTD/YTD Metrics!!!!

You are essentially trying to create a many to many transformation. For QTD you need to create a table with two columns. The transformation table essential transalates one input date into multiple dates that then joins to your fact table. An example of the entries in that table would be like the following:

Date            QTD_DATE
....             ....
03/31/2002       03/24/2002
03/31/2002       03/25/2002
03/31/2002       03/26/2002
03/31/2002       03/27/2002
03/31/2002       03/28/2002
03/31/2002       03/29/2002
03/31/2002       03/30/2002
03/31/2002       03/31/2002
04/01/2002       04/01/2002
04/02/2002       04/01/2002
04/02/2002       04/02/2002
04/03/2002       04/01/2002
04/03/2002       04/02/2002
04/03/2002       04/03/2002
....             ....

Notice that when the quarter changes in April it starts over on 4/1/2002. Everytime the quarter changes you start the days over on that month.

RE: QTD/YTD Metrics!!!!


thanks for your replies.... in my case the user will be selecting the year and month but not date.... i have a table with targets for each month day wise... say for ex. if the user selects 2002 and September in QTD Targets Metric it should aggregated till current date and not entire month which is what it is doing now because the user input is Year and Month.... my transformation table looks like this....

Quarter Month   Trans_Month
1    1    1
1    2    1
1    2    2
1    3    1
1    3    2
1    3    3
2    4    4
2    5    4
2    5    5
2    6    4
2    6    5
2    6    6
3    7    7
3    8    7
3    8    8
3    9    7
3    9    8
3    9    9
4    10    10
4    11    10
4    11    11
4    12    10
4    12    11
4    12    12

any suggestions on how to make it take only till the present date and not entire month... remember the user input will be only month and year....

thanks again


RE: QTD/YTD Metrics!!!!

    I think your problem can be solved by using filters. You need to create a filter which takes the current date and use it to display current month value and a filter to extract qtr from the date you have specified. this can be done if u have relations between month and quarter of year. If this works then there would be no problem in doing the other one i.e ytd

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! Already a Member? Login

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