Changing Week of year function

[D1234]

Hello,

I am currently working on a report where I am filtering a date field for it to show only the last four weeks of data. I am using a _week_of_year function with a  filter that looks like this:

_week_of_year ([Date]) between [Week of the Year Current Date] -5 and [Week of the Year Current Date] -1

I get double data using this. It is because it shows entries from Mondays and Sundays of the same week as two different weeks. Is there a way to set Cognos to only start weeks on Mondays in such a way to that this filter will show consistent information?

Thanks

[Lynn]

Hello,

I am currently working on a report where I am filtering a date field for it to show only the last four weeks of data. I am using a _week_of_year function with a  filter that looks like this:

_week_of_year ([Date]) between [Week of the Year Current Date] -5 and [Week of the Year Current Date] -1

I get double data using this. It is because it shows entries from Mondays and Sundays of the same week as two different weeks. Is there a way to set Cognos to only start weeks on Mondays in such a way to that this filter will show consistent information?

Thanks

The function definition indicates that the week begins on Monday so it should already be what you want. How is the data item [Week of the Year Current Date] defined?

_week_of_year ( date_expression )

Returns the number of the week of the year of "date_expression" according to the ISO 8601 standard. Week 1 of the year is the first week of the year to contain a Thursday, which is equivalent to the first week containing January 4th. A week starts on Monday (day 1) and ends on Sunday (day 7).

[D1234]

Thanks for the reply,

Week of the Year Current Date is defined as:

_week_of_year (current_date)

To clarify, this field is created only from functions.

[Lynn]

Thanks for the reply,

Week of the Year Current Date is defined as:

_week_of_year (current_date)

To clarify, this field is created only from functions.

Unless there is some database setting in your situation that alters the Monday - Sunday week definition then it should consider the week to be Monday through Sunday. You can try hard coding specific dates in the function to determine if the week is Monday to Sunday or Sunday to Saturday. You can also look at the generated SQL to see if there are any clues offered there.

Although not related to your original question, your filter expression is going to be problematic in January. When we get to week 1 of 2018 your filter will first determine the week number as 1 and then subtract to get the "between" elements. That will leave you looking for dates between weeks -4 to 0 which of course won't ever exist.

I would suggest you revise your filter to do the subtraction first and then determine the week number. Something like this:

Code Select Expand


_week_of_year ( [Date] ) between
  _week_of_year ( _add_days ( current_date, -35 ) )
  and
  _week_of_year ( _add_days ( current_date, -7 ) )


[D1234]

Thank you,

I will try looking into the settings to see if I can find anything. Additionally, thanks for the suggestion about the filter. Perhaps this may actually fix my current problem.