Bruce Leonetti asked 1 year ago
Title : How to get MTD and YTD data in Reports?

Question :

How to get MTD and YTD data in Reports?

1 Answers
nalinnalin Staff answered 1 year ago

1.Create a report with the required columns and also add columns such as Fiscal year and Julian month number.
2. Then select fiscal year column and go to Edit formula.
3. Enter the required fiscal year using repository variable to make it dynamic lets say we take as Previous fiscal year.
4. select the Julain month number column ,go to edit formula.
5. Enter this Formula:
CASE WHEN “Time”.”Julian Month Number”=VALUEOF(“CURRENT_JULIAN_MONTH_OBIA”)-12 THEN ‘MTD’ WHEN “Time”.”Julian Month Number” <= VALUEOF("CURRENT_JULIAN_MONTH_OBIA")-13 and "Time"."Julian Month Number" > VALUEOF(“PREVIOUS_FSCL_JULIAN_MON_OBIA”) THEN ‘MTD1’ ELSE ‘OTHERS’ END
where “CURRENT_JULIAN_MONTH_OBIA” and “PREVIOUS_FSCL_JULIAN_MON_OBIA” are the repository variables.
The above function is for Previous fiscal year.
In case of Current fiscal year, enter the current fiscal year in fiscal year column using rpd variable and below formula in Julain month number.
CASE WHEN “Time”.”Julian Month Number”=VALUEOF(“CURRENT_JULIAN_MONTH_OBIA”) THEN ‘MTD’ WHEN “Time”.”Julian Month Number” <= VALUEOF("CURRENT_JULIAN_MONTH_OBIA")-1 and "Time"."Julian Month Number" > VALUEOF(“PREVIOUS_FSCL_JULIAN_MON_OBIA”) THEN ‘YTD’ ELSE ‘OTHERS’ END

where MTD stands for Month till date is the current month of the corresponding fiscal year and
YTD stands for Year till date is the data of all month except the current month.
In the above scenario,edit report>selection steps>julain month number>start with MTD>>Add YTD.

Cubastion is among a very small number of specialized Siebel CRM Oracle Gold Partners in SME.