Dynamic MTD / YTD Measures in Siebel Analytics

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

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

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

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

Step4: Creating the dashboard prompt

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

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.

To know more about Siebel Analytics