A report we're working on should display a graph with on the X-axis the day of the week (Mon, Tue, Wed, etc), starting today with values that go up to a week back in the past.

Sounds simple, but we're stuck!
The items on the X-axis are based on date and should be ordered chronologically, not by day of the week number or alphabetically. Which is what we tend to get as a result...

For example, today (Wednesday) the X-axis should read: "Thu Fri Sat Sun Mon Tue Wed".

It's okay to add relevant extra information on the X-axis, such as the week number, but actual dates are considered too detailed.

I managed the below (the week number on the X-axis is extra), but it breaks around new year:

DEFINE FILE CALENDAR
 DoW/wt=DATECVT(DATE, 'I8YYMD', 'YYMD');
END
GRAPH FILE CALENDAR
SUM DOWNR
BY WEEKNR
BY DoW
WHERE DATE FROM 20120202 TO 20120208;
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 2
END

This message has been edited. Last edited by: Kerry,

Consider building a list of desired Weekdays in your desired order (Example: -SET &DaysOfWeek = 'xxx'; , which results in something like this: 'Sat' AND 'Sun' AND 'Mon' AND 'Tue' AND 'Wed' AND 'Thu' AND 'Fri') and using that in

ACROSS DoW AS '' COLUMNS &DaysOfWeek.EVAL

Ah, that does it. Thanks!
Added benefit: Any days in the week for which there was no record now shows up in the graph too.

I ended up with this code, which is probably a little suboptimal:

-SET &DOW = DOWK(&YYMD, 'A3');
-SET &WEEKDAYSMON = 'Mon AND Tue AND Wed AND Thu AND Fri AND Sat AND Sun';
-SET &WEEKDAYSTUE = 'Tue AND Wed AND Thu AND Fri AND Sat AND Sun AND Mon';
-SET &WEEKDAYSWED = 'Wed AND Thu AND Fri AND Sat AND Sun AND Mon AND Tue';
-SET &WEEKDAYSTHU = 'Thu AND Fri AND Sat AND Sun AND Mon AND Tue AND Wed';
-SET &WEEKDAYSFRI = 'Fri AND Sat AND Sun AND Mon AND Tue AND Wed AND Thu';
-SET &WEEKDAYSSAT = 'Sat AND Sun AND Mon AND Tue AND Wed AND Thu AND Fri';
-SET &WEEKDAYSSUN = 'Sun AND Mon AND Tue AND Wed AND Thu AND Fri AND Sat';
-SET &WEEKDAYS = &WEEKDAYS.&DOW;

DEFINE FILE CALENDAR
 DoW/wt=DATECVT(DATE, 'I8YYMD', 'YYMD');
END
GRAPH FILE CALENDAR
SUM DOWNR
ACROSS DoW COLUMNS &WEEKDAYS.EVAL
WHERE DATE FROM 20120202 TO 20120208;
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
END

quote:

which is probably a little suboptimal

Hmm, probably not ... I actually like what you did. Simple is better I guess.

One could get a bit more elaborate and do something like this:

-SET &DOW          = LCWORD(3, DOWK(&YYMD, 'A3'), 'A3');
-SET &WEEKDAYSLIST = 'Mon AND Tue AND Wed AND Thu AND Fri AND Sat AND Sun AND Mon AND Tue AND Wed AND Thu AND Fri AND Sat';
-SET &DAYPOS       = POSIT(&WEEKDAYSLIST, &WEEKDAYSLIST.LENGTH, &DOW, 3, 'I3');
-SET &WEEKDAYS     = SUBSTR(&WEEKDAYSLIST.LENGTH, &WEEKDAYSLIST, &DAYPOS, &DAYPOS + 50, 51, 'A51');

Less lines, yes. Optimal/efficient? debatable.

You probably don't need that .EVAL after &WEEKDAYS in the ACROSS line.

quote:

ON GRAPH SET HTMLENCODE ON

what does this do?
i'm only in 768 (sigh)
..
this is a kickin thread! brillliant

Good question! That command was inserted by the Advanced Graph editor, but it's not listed in our Help files it seems...

I assume it escapes characters that are special to HTML (and probably XML too). I doubt it would do much in PNG or JPEG output, but it could be important to SVG output as under the hood that is an XML format.

I did some quick tests, but it didn't seem to make any difference whether I set HTMLENCODE to ON or OFF...

Here is yet another way that you could put the weekdays in a list

-SET &WEEKDAYS='';
-REPEAT :WEEKLOOP FOR &N FROM 1 TO 7
-SET &WEEKDAYS=IF &N EQ 1 THEN LCWORD(3,DOWK(&YYMD+&N, 'A3'),'A3') ELSE &WEEKDAYS || ' AND ' | LCWORD(3,DOWK(&YYMD+&N, 'A3'),'A3');
-:WEEKLOOP

Nice solution, but that breaks on weeks that cross a month:

-SET &WEEKDAY1 = DOWK(20120229, 'A3');
-TYPE Last day of February: &WEEKDAY1
-SET &WEEKDAY2 = DOWK(20120230, 'A3');
-TYPE Next day (empty): &WEEKDAY2

Oops forgot that unlike with define fields it doesn't work like a smartdate. Changed it so it works.

-SET &WEEKDAYS='';
-REPEAT :WEEKLOOP FOR &N FROM 1 TO 7
-SET &WEEKDAYS=IF &N EQ 1 THEN LCWORD(3,DOWK(AYMD(&YYMD,&N,'I8YYMD'), 'A3'),'A3') 
-ELSE &WEEKDAYS || ' AND ' | LCWORD(3,DOWK(AYMD(&YYMD,&N,'I8YYMD'), 'A3'),'A3');
-:WEEKLOOP