Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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.
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.
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
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
Nope, this expression produces very strange sorting
Nope, produces sorting as seen below
Thank you very much everyone. You saved me HOURS!