QTD/YTD Metrics!!!!

[Sridharan]

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

thanks

 

[chael]

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

 

[reisw]

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.

 

[Sridharan]

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

sridharan

 

[suggest]

Hi,
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