Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: suhas_bhat on 04 Jan 2011 04:23:07 AM

Title: Filter to load previous 12 weeks data
Post by: suhas_bhat on 04 Jan 2011 04:23:07 AM
Hi all,
  I have a requirement of adding a filter to load the previous 12 weeks data.
We have coded this..
case when
_week_of_year ({sysdate}) >12
then [Calendar Week Number] < _week_of_year ({sysdate}) and [Calendar Week Number] >= _week_of_year ({sysdate})-13
when _week_of_year ({sysdate}) =<12
then
([Calendar Week Number] < _week_of_year ({sysdate}) and [Calendar Week Number] >= 52-(13-_week_of_year ({sysdate})))
end

How to take into consideration the change in year?? the expression 52-(13-week_of_year ({sysdate})) will give us the week for previous year..
I have a field called [Calendar year] too..
Please suggest any alternate way to do this if possible. ???
Title: Re: Filter to load previous 12 weeks data
Post by: sir_jeroen on 04 Jan 2011 09:21:14 AM
what about: 12 weeks = 12*7 =84 days and then  filter on

current_date between current_date - 84 and current_date
Title: Re: Filter to load previous 12 weeks data
Post by: cognostechie on 04 Jan 2011 01:37:51 PM
That's how I do it too.

[Namespace].[Query Subject].[Date] between _add_days (current_date,-84) and _add_days (  current_date)
Title: Re: Filter to load previous 12 weeks data
Post by: Lynn on 04 Jan 2011 01:56:57 PM
Nit-picky alert....

The "between" is inclusive so you are really requesting 85 days which is 12 weeks plus an extra day.

May or may not be an issue in your case but thought I'd mention it. Depending on which side needs adjustment, you could do either of the below. (Or just ignore the crazy lady and don't sweat it).

Code Select
between _add_days (current_date,-84) and _add_days (current_date,-1)

OR

Code Select
between _add_days (current_date,-83) and _add_days (current_date)


Title: Re: Filter to load previous 12 weeks data
Post by: sir_jeroen on 04 Jan 2011 02:02:09 PM
Women.... They always  have to complain :D
Anyway: Good that you mentioned this....
Title: Re: Filter to load previous 12 weeks data
Post by: Lynn on 04 Jan 2011 03:14:30 PM
I save it all up for people at work --- my husband thanks you all ;D
Title: Re: Filter to load previous 12 weeks data
Post by: cognostechie on 04 Jan 2011 03:57:13 PM
Yeah, my filter had (current_date, -1) but thought it may not apply in his case so I removed that while posting .

Just in case somebody needs other filters:

This Week - [Date]  between _add_days (  current_date, 0 - (_day_of_week (  current_date ,1 ))) and current_date

Last Week - [Date]  between
_add_days (  current_date, 0 - (_day_of_week (  current_date ,1 )) - 7) and
_add_days (  current_date, 0 - (_day_of_week (  current_date ,1 )) - 1)

This Month - [Date]  between   _first_of_month(  _add_months (  current_date,0)) and _last_of_month  (  _add_months (  current_date,0))

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

Rolling Month - [Date] between _add_months (  current_date,-1) and _add_days (  current_date, -1)

Rolling Week - [Date] between _add_days (  current_date,-8) and _add_days (  current_date, -1)

Not sure if I am using the most efficient functions so let me know if there is a better way..
Title: Re: Filter to load previous 12 weeks data
Post by: suhas_bhat on 05 Jan 2011 04:30:37 AM
thanks a lot guys, this worked brilliantly.. brilliant cause i think it covers leap years too  :)
Text only | Text with Images