topic Re: Sorting weekdays when comparing 7 days vs 7 days last year in QlikView

Sorting weekdays when comparing 7 days vs 7 days last year

Tue, 09 Jul 2013 06:12:01 GMT

Hi all,

I have a table chart comparing rolling 7 days this year with rolling 7 days last year. The problem is though that I need to compare weekday with weekday because sales are very different through the week.

For example, I am comparing Tuesday 09/07/2013 with Tuesday 10/07/2012.

The user can from a calendar pick a date and the rolling 7 objects start with showing sales for the selected date and 6 days prior to that date. That is all handled in the expressions

The problem is with the dimension for this chart. If have chosen weekday and I have problems with sorting the days in the correct order. I want the chosen weekday to be at the bottom and the prior days to go up.

In the example from above it should be:

Wed

Thur

Fri

Sat

Sun

Mon

Tue

Any help is very appreciated as I have struggled with this for quite some time now

Re: Sorting weekdays when comparing 7 days vs 7 days last year

rwunderlich — Tue, 09 Jul 2013 06:45:35 GMT

In the sort pane, select the Weekday field. Check "expression" for sort, and enter the datefield value as the sort expression.

-Rob

Re: Sorting weekdays when comparing 7 days vs 7 days last year

Tue, 09 Jul 2013 22:20:07 GMT

Hi Rob,

Thanks, but I have tried this and it gives me this result (see below) if I select 10th May 2013. The first day, in this case Friday is always correct but after that the sorting won't work. Any other ideas?

Re: Sorting weekdays when comparing 7 days vs 7 days last year

rwunderlich — Wed, 10 Jul 2013 02:04:17 GMT

I don't recognize the language of your weekdays, so I'm having a hard time interpreting your results. What language is it? Can you upload a sample qvw?

-Rob

Re: Sorting weekdays when comparing 7 days vs 7 days last year

Wed, 10 Jul 2013 03:13:42 GMT

Hi again,

Sorry, forgot about that.

The language is the QlikView-land language, Swedish

This will tell you what days they are:

DayNames ='Må;Ti;On;To;Fr;Lö;Sö';

The sort setting looks like this:

The expression for the rolling 7 days for current year looks like this:

Count( {$< Year=, Month=, Day=,Week=,Quarter=, Date={">=$(=Date(Max(Date-6)))<=$(=Date(Max(Date)))"}>} distinct Ordernr )

And for last year rolling 7 days:

Count( {$< Year=, Month=, Day=,Week=,Quarter=, Date={">=$(=Date(addmonths(Max(Date-5),-12)))<=$(=Date(addmonths(Max(Date+1),-12)))"}>} distinct Ordernr

Thanks

Re: Sorting weekdays when comparing 7 days vs 7 days last year

rwunderlich — Wed, 10 Jul 2013 23:46:42 GMT

Try

=min(Date)

for the sort expression.

-Rob

Re: Sorting weekdays when comparing 7 days vs 7 days last year

Thu, 11 Jul 2013 00:35:57 GMT

Thanks Rob, but that didn't change the sort order.

Any more ideas?

Asa