Filter to load previous 12 weeks data

[suhas_bhat]

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. ???

[sir_jeroen]

what about: 12 weeks = 12*7 =84 days and then  filter on

current_date between current_date - 84 and current_date

[cognostechie]

That's how I do it too.

[Namespace].[Query Subject].[Date] between _add_days (current_date,-84) and _add_days (  current_date)

[Lynn]

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 Expand

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

OR

Code Select Expand

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

[sir_jeroen]

Women.... They always  have to complain :D
Anyway: Good that you mentioned this....

[Lynn]

I save it all up for people at work --- my husband thanks you all ;D

[cognostechie]

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..

[suhas_bhat]

thanks a lot guys, this worked brilliantly.. brilliant cause i think it covers leap years too  :)