Sorting Time Dimension 1

[wawanz]

Hi all, I have a similar problem with thread960-584412. My time dimension have these attributes: Time_Key, Full_Date, Year_Num, Semester_Num, Qtr_num,...and so on. When I created the hierarchy, I have an attribute Week_Name (NameColumn) with Week_Num as the KeyColumn. I already used the OrderBy: Key property.

The problem is, when I browsed the dimension, it displayed the weeks in March in this order: 10,11,12,13,9. I solved this problem by using 2 digits for my Week_Num attribute. But that is my last resort, cause I don't want my users to read Week 01, Week 02, and so on. Is there any other solution guys?

Thanks a lot.

 

[SQLBI]

Hi,

The only thing i can think maybe causing this is the Data Type of your key column.

In the dimension editor, select your member key and click the 'Advanced' tab in the properties window. Then make sure your key data type is numeric and not text. For week numbers, i set mine to 'Small Integer' as you'll never need any more space if your only storing numbers upo 52.

Hope this helps.

Cheers,
Leigh

The problem with common sense is that it isn't that common!

 

[wawanz]

Hi Leigh, thanks for your help, it sorts fine now...turns out I forgot to set the Week_Num column's datatype to smallint in Dimension table...

Thanks a lot,
Wawan