Print Page - Filter to display last 12 complete months

Title: Filter to display last 12 complete months
Post by: D1234 on 04 Jan 2018 12:16:45 PM

Hello,

I need to create a filter which will automatically give me the information for the last 12 complete months. For example, since today is January 4, 2018, I need to exclude January thus far.

Could I accomplish this with something like:

_month_of_year ( [Date] ) between
_month_of_year ( _add_days ( current_date, -X ) )
and
_month_of_year ( _add_days ( current_date, -X ) )

Thanks

Title: Re: Filter to display last 12 complete months
Post by: Lynn on 05 Jan 2018 01:53:16 AM

This might work for you:

Code Select


[Date] between 
   _first_of_month ( _add_months ( current_date, -12 ) )
   and
   _last_of_month ( _add_months ( current_date, -1 ) )

Title: Re: Filter to display last 12 complete months
Post by: Invisi on 05 Jan 2018 02:30:34 AM

And if your date dimension has a first day of month and last day of month column, you can use those in Lynn's suggestion to have a bit fewer functions.

Title: Re: Filter to display last 12 complete months
Post by: D1234 on 05 Jan 2018 07:42:30 AM

Thanks,

I ended up getting it to work with this filter:

[Date] >_first_of_month (_add_months (current_date,-12))
and
[DateVisit
< _last_of_month (_add_months (current_date,-1))

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: D1234 on 04 Jan 2018 12:16:45 PM