Retrieve the last 13 weeks using _week_of_year function

[dfcrj]

I'm having a bit of an issue. i need to show the sales of an item for the last 13 weeks. We have a time dimension and a field called 'Calendar Week'. I tried to create a filter using this field and the function '_week_of_year' but can not get it to. In fact, I cant get the to display the current week, but I know its me since I'm new to cognos. thanks for the help!

 

[blom0344]

_week_of_year([somedate]) = _week_of_year(current_date) and
extract(year,[somedate]) = extract(year,current_date)

would be enough to fetch data based on the weeknumber of the current date (and taking care just to select this year's data)

Expanding this , is a little tricky if the 13 week period falls in 2 years.

Ties Blom

 

[dfcrj]

Thanks for the reply, I was able to get the results I need after many attempts.

I created two data items:
[CurrentWeek] - _week_of_year(getdate())
[PriorWeeks] - _week_of_year(getdate())-12

The created a filter using the two and made sure [calendar week] was included in the report to show the weeks.

[Calendar Week] <= [CurrentWeek] and [PriorWeeks] >= [Range1]

this worked beautifully and it's always updating itself.