Time Dimension 2

[thabrane]

Is there a way to put a count on a time dimension?

I have an All weeks time dimension which simply goes from years to weeks. I would like the short name for the weeks to be the number of the week in relation to the year. For instance, the week starting 1-1-04 is week # 1 and the week starting 2-2-04 is week # 6.

Any body have any idea how I could accomplish this task?

 

[nagrajm]

In order to create a label for weeks from 1-52, you will have to create a calculation in the query to convert a date to it's corresponding week no.

Use this calculated column for "Label Column" in the properties for Level - Week in the date dimension in transformer.

HTH.

 

[thabrane]

Will my data definition for the column in the iqd have to be defined for every week? For instance - if(Time = 2004-01-01)then (1) else if(time = 2004-01-08) then (2)....etc, for all 52 weeks?

This will make for a really long definition.

 

[nagrajm]

No. If you are using impromptu with Oracle, for example, use the Week() function on the date to extract the week no.

WEEK_NO = WEEK(SALE_DATE)

Use the WEEK_NO column in the transform as "Label Colum" in the property sheet for Week Level in the date dimension.

I hope it is clear.

 

[CHIUNTA]

Does the WEEK() function return the week number based on the calendar week or just the number of every 7 days from the beginning of the year?

Date WEEK(Date)
1/1/2004 Thu. 1
1/2/2004 Fri. 1
1/3/2004 Sat. 1
1/4/2004 Sun. 1
1/5/2004 Mon. 1 or 2?

 

[DoubleD]

Calendar Week with Sunday being the beginning of the week.
2004-01-01 1
2004-01-03 1
2004-01-04 2

If you want Weeks in groupings of 7 days, use the following Calc:
Round-Up((DayOfYear(DATE)/7),0)
This will return the following:
2004-01-01 1
2004-01-07 1
2004-01-08 2


Pain is stress leaving the body.

DoubleD