Creating Time Dimesion

[stevekoz]

Is there a method by which you can create a new time dimension within Report Studio.

I have a cross tab that pulls back currently Total Sold by Months.  But the report i am creating needs to break that down to totals by calendar weeks.  But we don't have weeks as dimension so is there a way of creating a dimension based on months that breaks it down to weeks manually?

[blom0344]

'Week' is not a easy definition as multiple definitions exist , especially in year transitions. Breaking down months to weeks based on month itself is of course impossible. _day_of_week()  may be of some use,combined with the monthnumber, but distribution of 52/53 weeks over 12 months is tricky at best.

The one true solution is adding a calendertable to the model with the week precalculated for each single date. In case you wonder why Cognos does not offer a  _week_of_year function ?  Well, for the above reasons

[absriram]

Cognos does offer a _week_of_year() function.

From Report Studio Professional Authoring User Guide:

_week_of_year:

Returns the number of the week of the year of "date_exp" 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).

Syntax
_week_of_year ( date_exp )

Example

_week_of_year ( 2003-01-01 )

Result: 1

You can use this for a new data item and nest it under the months.  However, there isn't a week of month function available so your week number will range from 1 to 52 for example.

Sriram.
http://www.cognosonsteroids.com

[stevekoz]

The week_of_year function works to a point but not exactly how i need it.

Currently the crosstab i have pulls back volume by month. However i need to break those volume totals down from Month to weeks when weeks aren't available in my data set.

Is it possible to write that out - anyone have an example ?

Thanks

[Lynn]

Are you getting just an aggregate figure for the entire month? Seems to me all you can do is divide that figure by the number of days in the month and then aggregate it back up to weeks. Otherwise figure out the weekly average based on your monthly data. You can't really say your volume for any given week is what you will be reporting because you have no idea which day or week the sales fall into. You can only assume.

The weeks that span months is the tricky business. I would think some expressions involving _week_of_year and case logic could do the trick.