YTD and MTD Prompts

Here are code samples for creating YTD and MTD prompts in the universe designer,the prompts are written for Oracle :

1. YTD

This prompt allows the user to enter a date and get all the dates from the month of the chosen date till the beginning of the current month, for example:

If the user will type 05/05/2010 (mm/dd/yyyy) he will get the range: 05/01/2010 till the first day of the current month.

Code:

Table.date BETWEEN trunc(To_Date(@Prompt('Enter Date:','D',,Mono,Free),'mm/dd/yyyy hh24:mi:ss'),'mm') AND TRUNC(SYSDATE,'MM')

2. YTD specific year:

This prompt allows the user to enter a date and get all the dates from the first day of the year of the chosen date till the chosen date, for example:

If the user will type 05/05/2011 (mm/dd/yyyy) he will get the range: 01/01/2011 till 05/05/2011.

Code:

Table.date between trunc(to_date(substr(@Prompt('Pick date','D','folder\date_object',mono,free),1,10),'mm/dd/YYYY'), 'YEAR') and to_date(substr(@Prompt('Pick target date','D','folder\date_object',mono,free),1,10),'mm/dd/YYYY')

3.MTD

This prompt allows the user to enter a date and get all the dates from the first day of the month of the chosen date till the chosen date, for example:

If the user will type 05/05/2011 (mm/dd/yyyy) he will get the range: 05/01/2011 till 05/05/2011.

Code:

trunc(Table.date) BETWEEN trunc(to_date(@Prompt('Enter Date:','A',,mono,free),'DD/MM/RR'),'MM')

AND to_date(@Prompt('Enter Date:','A',,mono,free),'DD/MM/RR