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
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
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
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
That did it! Thank you very much!
Abdel