Problem sorting dimension with generated dual date

[benjamins]

I'm using a dual Year&Week as the only dimension of a bar chart (see attachment).

I load Year&Week in script as

Dual(Year(DateId)&' '&Week(DateId),WeekStart(DateId)) as YearWeek

DateId is a plain date

I set the dimension's sorting order to numeric ascending as I would have expected the numeric value of the dual YearWeek to be used. However this does not seem to be the case as the result is:

2012 52

2012 1

2012 2

2012 3

...

2013 51

2013 52

2013 1

When loading years 2012 and 2013.

I've tried lots of different approaches from using other sorting orders up to interpreting YearWeek as string only. None of which yielded a correct result.

Anyone any idea what I'm overlooking?

Thanks and cheers

Benjamin

[#2 · Accepted solution]

I think that your problem there is that you are using the year() method instead of the WeekYear() method.

Example with 1 of January of 2012:

Year(): 2012

WeekYear(): 2011

Week(): 52     --> Because that day is considered part of the las week of 2011.

[jfkinspari]

The reason to why it's sorting as it is, is because of the output of the function Weekstart, which you use in the Dual formula. I expect you think it gives an integer, but's it's actual giving a date, and as 1st Jan 2012 belongs to a week starten in 2011, it gives the smallets value. Further more it's has to do with 1st Jan 2012 belongs to weeknumber 52 - given the used method.

[benjamins]

Thanks to the both of you for your explanations!

So,

Dual(WeekYear(DateId)&' '&Week(DateId),Num(Date#(WeekStart(DateId)))) as YearWeek,

seems to yield correct results. However I'm a bit confused as to wether this really is the correct usage of the Dual() function now!? I would have thought that passing an actual date as the function's second argument would cause the numeric representation to be used.

[CELAMBARASAN]

Try with this Expression for your case

Dual(Year(DateId)&' '&Week(DateId),Num(Year(DateId)&Week(DateId))) as YearWeek,

You cann't use week start here.

[jfkinspari]

The second part of the dual funktion, Num(Date#(WeekStart(DateId)))), will make no difference.

It's indeed using the numeric rep.

I think you'll be good just be replacing the year function as IOSU explained

[kaushiknsolanki]

Hi,

     Do one thing, create another filed called Week.

     Now in your chart go to the sort tab and choose the expression.

     Here enter the field name week and sort it in ascending order.

     And remove other sorting options.

     Hope this will solve your problem.

Regards,

Kaushik Solanki

[benjamins]

Nope, this expression produces very strange sorting

[benjamins]

Nope, produces sorting as seen below

[benjamins]

Thank you very much everyone. You saved me HOURS!