Stuck Comparing This week with last week - sort order?

Wed, 20 Jul 2016 10:12:13 GMT

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

Re: Stuck Comparing This week with last week - sort order?

Gysbert_Wassenaar — Wed, 20 Jul 2016 15:55:48 GMT

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

Re: Stuck Comparing This week with last week - sort order?

Thu, 21 Jul 2016 09:04:54 GMT

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.

Re: Stuck Comparing This week with last week - sort order?

Gysbert_Wassenaar — Thu, 21 Jul 2016 11:32:52 GMT

Perhaps the attached example helps.

Re: Stuck Comparing This week with last week - sort order?

Thu, 21 Jul 2016 12:09:03 GMT

Thank you Gysbert - Spot on!

topic Re: Stuck Comparing This week with last week - sort order? in QlikView

Stuck Comparing This week with last week - sort order?

Re: Stuck Comparing This week with last week - sort order?

Re: Stuck Comparing This week with last week - sort order?

Re: Stuck Comparing This week with last week - sort order?

Re: Stuck Comparing This week with last week - sort order?