Sorting by WeekYear field

[Anonymous]

Hello everyone,

I have a week year field in my data that's generated as part of a master calendar:

Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as TimeSheet.WeekYear

My problem is that I can't sort by it so it's obviously not being interpreted as a date. I've tried playing around with NUM and DATE# but haven't managed to find anything that works. I did try changing it to a dual in the script but it messed up the grouping and I ended up with separate rows for each day of the week.

Ideally I would like a quick fix that I can implement in the chart directly rather than the script as the document takes a long time to reload and I'm on a bit of a deadline!

Thank you in advance!

[sunny_talwar · Accepted solution]

For front end fix, may be try this as your sorting Expression:

Dual(TimeSheet.WeekYear, MakeWeekDate(SubField(TimeSheet.WeekYear, '-', 2), SubField(TimeSheet.WeekYear, '-', 1)))

MakeWeekDate(SubField(TimeSheet.WeekYear, '-', 2), SubField(TimeSheet.WeekYear, '-', 1))

[Anonymous]

why sorting by TempDate is not sufficient?

if not enter in sort tab: sort by Expression year(TempDate)&week(TempDate)

[Anonymous]

Brilliant, I thought MakeWeekDate might be the one but couldn't figure out how to make it work. Thank you!