Stuck Comparing This week with last week - sort order?

[#1]

Hi,

I am trying to create a grouped bar chart with the days of the week as the dimension.

I have done the set analysis and can produce the results that I need, except for the sort order!

It will be a rolling chart and the latest day (yesterday) should always appear on the right.  This would be easy if I was using a date value, but then the columns would not group by day (i.e. I will have 14 days along the bottom in stead of 7).

I need something like:

This is fine, except today is Wed and the days at the bottom should run Wed to Tue (latest full day of data on right), Tomorrow I want then to run Fri to Thu etc.

I am using a Calendar table to return the standard day of the week (Mon, Tue etc) but can not sort it correctly for each day.

=if((weekday(max([Arrival Date]))) = 'Mon', wildmatch([Cal Day Of Week],'Tue','Wed','Thu','Fri','Sat','Sun','Mon')

,if((weekday(max([Arrival Date]))) = 'Tue', wildmatch([Cal Day Of Week],'Wed','Thu','Fri','Sat','Sun','Mon','Tue')

,if((weekday(max([Arrival Date]))) = 'Wed', wildmatch([Cal Day Of Week],'Thu','Fri','Sat','Sun','Mon','Tue','Wed')

,if((weekday(max([Arrival Date]))) = 'Thu', wildmatch([Cal Day Of Week],'Fri','Sat','Sun','Mon','Tue','Wed','Thu')

,if((weekday(max([Arrival Date]))) = 'Fri', wildmatch([Cal Day Of Week],'Sat','Sun','Mon','Tue','Wed','Thu','Fri')

,if((weekday(max([Arrival Date]))) = 'Sat', wildmatch([Cal Day Of Week],'Sun','Mon','Tue','Wed','Thu','Fri','Sat')

,if((weekday(max([Arrival Date]))) = 'Sun', wildmatch([Cal Day Of Week],'Mon','Tue','Wed','Thu','Fri','Sat','Sun')

)))))))

Can any one advise where I am going wrong or if there is another way of doing this?

Many thanks.

Dave

[Gysbert_Wassenaar]

Use Arrival Date as dimension and format as Date([Arrival Date],'WWW')

[#3]

Thank you Gysbert for your reply.

When I use a running date formatted as WWW it produces the following with this weeks (green) appearing after last weeks (blue):

I was hoping to get them side by side.

Thanks again.

[Gysbert_Wassenaar · Accepted solution]

Perhaps the attached example helps.

[#5]

Thank you Gysbert - Spot on!