Weeks to display on Date

[Abdel]

Hi there,

On a cross tab report, I am desperately trying to display multiple weeks on my columns, like this: Week1, Week2, Week3, Week4, etc


The column field is essentially a Date type field named Date1 and I for the life of me cannot seem to get the weeks to format as I want.


Report should look as follows:


                                        Week1        Week2         Week3
English TV Commercial:         2                  5                9
Spanish TV Commercial          7                 9                6

Objective of Report is to show impact of commercial on sales.

Please help!

A

[cognos810]

Hello Abdel,
Do you need to identify the week of the date in the field Date1, or is it already a single date from every week. Meaning, Does your Date1 field return continuous dates like Jan 1, 2015, Jan 2 2015, Jan 3 2015....etc
OR is it Jan 1, 2015, Jan 8 2015, Jan 15 2015.....?

If the dates being returned are already from distinct weeks, then all you need to do is sort the column on the date1 field, and then change the column title type to report expression. The expression inside would be:
'Week ' + number2string(ColumnNumber ())

-Cognos810

[Abdel]

Hello there

Thanks for your response! I was beginning to lose hope and not mention spinning my wheels.

To answer your questions:  I need to identify the week of the date in the field Date1

My date field contains values as follows:

2015-01-22
2015-01-28
2015-01-29
2015-02-06
2015-02-06
2015-02-09
2015-02-09
2015-02-09

[cognos810]

In that case, create a data item(eg: [Week Number]) with the expression as _week_of_year([Date1]),
and put that as column for the crosstab. Then sort it ascending on [Week Number].
Unlock the column and insert a text before the [Week Number] item with text 'Week ' in it.

If you need to show it sequentially as Week1, Week2, ...etc. then you can employ the previous method using the report expression, 'Week ' + number2string(ColumnNumber ())

Would it work for you?

-Cognos810

[Abdel]

That did it!  Thank you very much!

Abdel