Custom weeks in the time dimension in Cognos Transformer

[nkn@123]

Hi ,

I need to modify the existing time dimension which was based on a lunar calendar using 4-5-4 weeks.

The requirement is to modify this time dimension with a new calendar that comes from a table (Columns : calendar Year/Month/Day , New calendar Year/Month/Day/Quarter/Week) , but this calendar adds the 53rd week for year 2016 in the first month rather than in the last month. The table is populated in such a way that 2016 Q1 has 5 weeks, 5 weeks and 4 weeks.

So effectively the start of 2016 Q1 has the week starting from 4/25/2015 till 5/29/2015 thus having 35 days and 5 weeks for the first month

So the first Q1 of 2016 follows 5-5-4 instead of 4-5-4 and the subsequent Quarters all follow the standard 4-5-4.

How do I change the existing time dimension in Cognos transformer to implement the New Calendar .

Please note that the existing time dimension also has relative time categories so when it is replaced with the new calendar the relative time categories should not change.

Appreciate if someone could provide the steps to implement this as I am kind of new to this.

Regards,
NKN

[bdbits]

I would probably leave the existing calendar in place and create an alternate drill path for the new calendar. We often have at least three separate drill-down paths in our standard time dimension - federal fiscal, state fiscal, and standard calendar. (I am in state government in the US.) Then users can choose what they need for a particular reporting scenario. So when you open up "all time" you see the 3 different calendars, then open the one you want to use for selecting the year/quarter/month/week/day.

I am not clear if this would work for your users or requirements. If so I can give you more detail about how to implement it.

[nkn@123]

Hi ,

Thank you for your suggestion. But I need a separate time dimension

I had to edit the existing time dimension so that the existing time categories don't change. There are users who use the reports and the MUN should remain the same and the reports using these relative time categories shouldn't break.

I used the database columns such as Fiscal week , Fiscal month , Fiscal Quarter , Fiscal Year. I changed the time function to None and unchecked the unique box at each level.

Though the relative time categories are generated with same MUN and don't break any reports already using them , there seems to be some discrepancy.

Eg. In the Current Month level of relative time category the next level is week ie 2015/02/14 . It should state 2015/Feb and then the weeks beneath it .

Same goes for QTD level - the next level should be 2015 Q4 . Instead it is 2015/Feb.

Rest of the other categories seem to work fine.

Any idea why some of the levels are getting dropped.

Is it because I am not using the time function and setting it to None for each level.

I tried keeping it to Day , Week , Calendar month but it doesn't give me the required result of 5-5-4 weeks in the first quarter which I need ie it doesn't pick the database values .

Any help will be appreciated.

Regards,
NKN