topic Re: Sort weekday with specific date last in QlikView

Sort weekday with specific date last

Mon, 18 May 2015 10:33:34 GMT

Hi all,

I have a problem with sorting weekday. I want to sort the days of the week with a specific day as the first day to see.

For instance if i want to show the last 7 days and today would be a wednesday i want the sort order to be like:

Weekday
tuesday
monday
sunday
saterday
friday
thursday
wednesday

this makes it easier to read a chart that shows the workload from the last 7 days.

Could anybody help me with this?

My thanks in advance for the help!

Re: Sort weekday with specific date last

Mon, 18 May 2015 10:37:48 GMT

i also see uses for this with a specific date. lets say i want to show the last 7 days from last month i want the order to be:

thursday 30-4

wednesday 29-4

tuesday 28-4

monday 27-4

sunday 26-4

saterday 25-4

friday 24-4

Re: Sort weekday with specific date last

amit_saini — Mon, 18 May 2015 10:38:52 GMT

Tom,

Change here (Back end) and check:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Thanks,

Re: Sort weekday with specific date last

jonathandienst — Mon, 18 May 2015 10:55:19 GMT

If you have a calendar with DayofWeek as a field (in addition to the normal Date, Month, Year etc fields), then you can dimension the chart/table with DayofWeek and sort the dimension by Date (ascending).

Re: Sort weekday with specific date last

Mon, 18 May 2015 10:55:19 GMT

As you can see i already have those. I just have them in Dutch (becouse me and my customers live in the Netherlands and prefer a Dutch sollution)

Re: Sort weekday with specific date last

Mon, 18 May 2015 11:00:21 GMT

that doesn't work.

The chart i want to use it on has the following expressions and dimensions:

Expression 1:

= count({1}DISTINCT if(Date>=Date('13-03-2014')-6 and Date<=Date('13-03-2014'), OrderID))

Expression 2:

=num(count(OrderID) / count( distinct if(OrderID>0, weeknumber & YYYY)),'#.###,##')

Dimension:
= Weekday (and yes this is from a calender file and not the weekday function of QV)

Re: Sort weekday with specific date last

tresB — Mon, 18 May 2015 11:16:37 GMT

Sort by expression, something like:

If(Weekday(DateField)<WeekDay(Today()),Weekday(DateField),Weekday(DateField)-7)

Re: Sort weekday with specific date last

Mon, 18 May 2015 12:05:51 GMT

i tried it like this:

=if(Date<=date('13-03-2014') and Date>=date('13-03-2014')-7, Weekday)

this works when i show this in a listbox but nog in the chart.

As discribed above, the chart is build with the following Expressions and Dimensions:

Expression 1:

= count({1}DISTINCT if(Date>=Date('13-03-2014')-6 and Date<=Date('13-03-2014'), OrderID))

Expression 2:

=num(count(OrderID) / count( distinct if(OrderID>0, weeknumber & YYYY)),'#.###,##')

Dimension:
= Weekday (and yes this is from a calender file and not the weekday function of QV)

Re: Sort weekday with specific date last

tresB — Mon, 18 May 2015 12:25:57 GMT

May be like attached sample qvw? The expression you use in the chart should not much affect that way.

PFA

If(WeekNum<WeekDay(Today()),WeekNum,WeekNum-7)

For testing and not having to wait if this works tomorrow or not, you can simply test it with trying Today()+1, today()+2...in place of today() itself.

Re: Sort weekday with specific date last

Mon, 18 May 2015 13:32:14 GMT

in my document he shows sat, sun, mon, tue, wed, thu, fri.

Doesn't matter what date i take

Re: Sort weekday with specific date last

jonathandienst — Mon, 18 May 2015 15:00:55 GMT

I suggest that you post a sample with some representative data and the chart under discussion. That way we can all stop guessing....

Re: Sort weekday with specific date last

Mon, 18 May 2015 15:53:09 GMT

Thanks Tresesco, with your help i managed to fix it! Your document helpt me very much!

It works now with the following stats:

Dimension:

Weekday

Expression 1:

count({1}DISTINCT if(Date>=Date('13-03-2014')-7 and Date<Date('13-03-2014'), OrderID))

Expression 2:

num(count(OrderID) / count( distinct if(OrderID>0, Weeknumber & Year)),'#.###,##')

Sort Expression:

If(Weekdaynr<WeekDay(date('13-03-2014')+1),Weekdaynr,Weekdaynr-7)