Definition:

MTD: Acronym for Month to Date. MTD value of a certain measure would be the aggregated total of the measure from the start of the month till the defined date. For example: For the date 15th August 2008, MTD Dollars would be the total dollars from starting of August 2008 i.e. 1st August 2008 to 15th August 2008.

YTD: Acronym for Year to Date. YTD value of a certain measure would be the aggregated total of the measure from the start of the year till the defined date. For example: For the date 15th August 2008, YTD Dollars would be the total dollars from starting of 2008 i.e. 1st January 2008 to 15th August 2008.

Situation:

The client required MTD YTD metrics for a few reports. The twist in the requirement was the calculation based on user specified dates supplied from the dashboard prompt instead of the usual current date MTD YTD metrics. This means capturing the date value entered by the user and applying its relation to the concerned request.

Solution:

After a lot of brainstorming and trying out N number of unsuccessful RPD and Answers permutations and combinations, the solution was arrived at with the help of “Filter based on other request” option in the Answers section and a dummy request to capture the prompted date.

Implementation:

Step1:  Creating the dummy report

Dynamic MTD

This dummy request is used to capture the Transaction date. As seen, the Transaction Date column is prompted and the dashboard prompt date value is fed to this request. We also take Month and Year columns for MTD YTD calculation purposes.

Step2: Creating the MTD Request

Dynamic MTD

The request shows Amount by Pay Type. We want to calculate MTD amount for a date specified by the user. The Date filter columns of the dummy and the main request should be same.

Step3: Creating MTD specific filters

Dynamic MTD

Using the “Filter based on Another Request”, we specify the filters for MTD calculation.

Step4: Creating the dashboard prompt

Dynamic MTD

It’s always a good practice to specify a default value to the dashboard prompt so that the dummy request is initialized to some value. Needless to say, the dashboard prompt, the dummy request and the main request should come from the same subject area.

Step5: Displaying and verifying the MTD metrics

Dynamic MTD

The MTD request has been placed on the dashboard along with the normal reporting date request. This way one can verify the MTD results quite efficiently.

Conclusion:

  • The YTD metrics can be calculated in a similar manner. The month filter won’t be applicable for YTD. Rest remains unchanged.
  • Combine request feature of Answers should be used if the reporting date, YTD and MTD metrics need to be shown together in one single request.
  • This dummy request feature is an excellent way of capturing the user entered prompt values of any data type and manipulating the report metrics on the fly based on the captured prompt value in the dummy request.

To summarize, via this method, we get the power of the presentation variables of OBIEE, strictly to just some extent, in Siebel Analytics.

Posted by Vimarsh Ganju
Comments (2)
August 29th, 2008

Comments (2)

Ramesh Anchula - June 10th, 2011

send me any updates OBIEE

archana - March 31st, 2009

Hi, Its really nice post on MTD and YTD measures. i have a question in analytics .appreciate if you could answer . 1.suppose i have 2 rpd 's and user1 shud access rpd1 and user 2 shud access rpd2.is it possible to access to different rpd's by 2 different users at the same time?

Comments are closed.