Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Sort weekday with specific date last

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!

Tags (4)
1 Solution

Accepted Solutions
Not applicable

Re: Sort weekday with specific date last

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)

11 Replies
Not applicable

Re: Sort weekday with specific date last

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

amit_saini
Honored Contributor III

Re: Sort weekday with specific date last

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,

AS

MVP
MVP

Re: Sort weekday with specific date last

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).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Sort weekday with specific date last

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)

2015-05-18_1252.png

Not applicable

Re: Sort weekday with specific date last

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)

MVP
MVP

Re: Sort weekday with specific date last

Sort by expression, something like:

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

Not applicable

Re: Sort weekday with specific date last

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)

MVP
MVP

Re: Sort weekday with specific date last

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.

Not applicable

Re: Sort weekday with specific date last

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

Doesn't matter what date i take

Community Browser