1 of 1 people found this helpful
Would creating a calculated field similar to the following and sorting it appropriately work?
datename('month',[Order Date]) + ' Week ' + datename('week' ,[Order Date])
I've a created a calculated field as you suggest -
'Week ' + datename('week',[Enrolment date]) + ', ' + datename('month' ,[Enrolment date])
- and sorted it. Enrolment date has a fiscal start date of May, which doesn't seem to be reflected in the week numbers, e.g. I would expect the first in the sorted list to be Week 1, May, instead of Week 1, January.
Thanks for all your help here
This is common as the fiscal start is much more of just changing the recognition of the dates rather than the dates themselves. When we run a formula on top of it, it tends to revert back to the Gregorian calendar convention rather than picking up our new fiscal start date. This is fine, since we can still mimic the fiscal date shift with a small change to formula:
'Week ' + datename('week',dateadd('month',-4,[Enrolment date])) + ', ' + datename('month' ,[Enrolment date])
Hope this helps!
Yes, that works!
Thanks again ... where are you as a matter of interest? It's Saturday evening here in London - I hope you're not going to spend all day Tableau-ing too!