4 Replies Latest reply: Jul 21, 2016 8:09 AM by David Sturdy RSS

    Stuck Comparing This week with last week - sort order?

    David Sturdy



      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.