Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Perhaps the attached example helps.
Use Arrival Date as dimension and format as Date([Arrival Date],'WWW')
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.
Perhaps the attached example helps.
Thank you Gysbert - Spot on!