Problem with 53rd week

[#1]

Hi,

I have a chart in my app which has weeks as a time dimension. 2015 had 53 weeks and week 53 spans the end of 2015 and beginning of 2016. Now, the weeks in 2015 are sorted correctly (49,50,51.. etc.) but the weeks in 2016 are not. Week 53, which in this case is before the first week of the year should come first:

How can I sort the time dimension so that the weeks appear in correct order? In addition, this would also cause problems if year 2016 would have 53 weeks. Luckily it doesn't, but is there something I could do to handle these kind of exceptions correctly?

[jonathandienst · Accepted solution]

You may want to create a week sequence number in the load script using the week number and the year - it seems you are doing that so just tweak the definition to make a dual value with the correct sort sequence:

LOAD Year,

     WeekNo,

     Dual(WeekNo & "\" & Year, Year * 100 + WeekNo) As WeekSeq,

     ...

Now use WeekSeq  as the dimension.

[sfatoux72]

Hi Pekka,

Use weekyear() instead of year() to have the corerect year corresponding to each week.

Week(date) & '/' & WeekYear(date)

[#4]

Hi!

Thank you very much! Both were working solutions. I ended up using the one suggested by Jonathan.