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

      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:

      upload.JPG

      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