CUSTOM PREDEFINED DATE RANGE FILTERS (WTD/MTD/QTD/YTD) in BO

CUSTOM PREDEFINED DATE RANGE FILTERS

In many financial reports, the requirement is to have date filters like user has to give start date and end date to see the data in the given time frame. But if user wants to run the report for a given period like for a month, quarter or year he will not be interested to calculate the start date of a week/month/quarter/year and give as input to the report. Instead he will be interested to say that he want data for a week/month/quarter/year.

These kind of implementations are done from many years but I have not found any document explaining the steps to implement this. So though it will be helpful for someone. As am new to documentation, please feel free to give your inputs to improve upon.

To achieve this, we generally follow the terminology as ‘WTD’,’MTD’,’QTD’, and ‘YTD’

WTD – Week to Date: Starting of Week to the current Date

MTD – Month to Date: Starting of Month to the current Date

QTD – Quarter to Date: Starting of Quarter to the current Date

YTD – Year to Date: Starting of Year to the current Date

Here the current date is the date on which the user is running the report.

Solution:

We can achieve this by using conditional object in the universe level. The syntax will change based on the type of data base which you are connecting to

Oracle:

1. Table.DATE between case

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’WTD’ then TRUNC(sysdate, ‘DAY’)

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’MTD’ then TRUNC(sysdate, ‘MONTH‘)

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’QTD’ then TRUNC(sysdate, ‘Q’) 

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’YTD’ then TRUNC(sysdate, ‘YEAR’) 

END

and sysdate

SQL Server:

1. Table.DATE between case

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’WTD’ then DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’MTD’ then DATEADD(month,DATEDIFF(month,0,GETDATE()),0)

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’QTD’ then DATEADD(q,DATEDIFF(q,0,GETDATE()),0)

when @Prompt(‘Enter value’,’A’,{‘WTD’,’MTD’,’QTD’,’YTD’},Mono,Free,Not_Persistent)=’YTD’ then DATEADD(year,DATEDIFF(year,0,GETDATE()),0)

END

And getdate()

Here user will be prompted to select one of the value from list ‘WTD’,’MTD’,’QTD’,’YTD’ and based on the selection he will get the data for the given date range.

Note: for WTD the Important difference in Oracle and SQL is Oracle considers the starting day of the week as Sunday where SQL server takes it as Monday. So while calculating the WTD you need to keep this in mind and confirm with your business analyst about the requirement.

Other custom date range requirements will be like Last 3 months and Last 6 months.

You can achieve them as well in similar way but only thing is you need to tweak the start date of the date range based on the period.

For SQL Server:

Last 6 Months

Dateadd(month,-6,getdate()) And end date will be getdate()

Ex: If todays date is 10-March-2016 then the start date will be 10-Sep-2015 and date will be 10-March-2016

Last 3 Months

Dateadd(month,-3,getdate()) And end date will be getdate()

Ex: If todays date is 10-March-2016 then the start date will be 10-Dec-2015 and date will be 10-March-2016

For Oracle:

Last 6 Months

Start date will be equal to add_months(sysdate,-6)

And

End date will be sysdate

Last 3 Months

Start date will be equal to add_months(sysdate,-3)

And

End date will be sysdate

Last 6 Quarters

Option 1:

Start date will be add_months(sysdate,-18)

And

End date will be sysdate

Option 2:

            table.year*100 + table.month between extract(year from sysdate)* 100+extract(month from sysdate)

             and extract(year from add_months(sysdate,-18))*100+extract(month from add_months(sysdate,-18))

Last 6 Quarters Excluding current Quarter

Option 1: table.date between trunc(sysdate,’q’)-1 and add_months(trunc(sysdate,’q’),-18)

Option 2:

     table.year*100 + table.month between

          extract(year from trunc(sysdate,’q’)-1)*100+

          extract(month from trunc(sysdate,’q’)-1)

          and

          extract(year from add_months(trunc(sysdate,’q’),-18))*100+

          extract(month from add_months(trunc(sysdate,’q’),-18))