[CLOSED] Sorting Sunday thru Saturday

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1877036196

September 10, 2019, 05:53 PM

AMC2

[CLOSED] Sorting Sunday thru Saturday

In the App Studio GUI, if I want to sort some data Sunday thru Saturday using an ACROSS instead of Monday thru Sunday with a DEFINE field that has the day of the week, how would I do that?

This message has been edited. Last edited by: FP Mod Chuck,

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 11, 2019, 08:09 AM

MartinY

This is one way

DEFINE FILE abc
DAYNO /D1   = DECODE DAYNM ('Sunday' 1 'Monday' 2 'Tuesday' 3 'Wednesday' 4 'Thusday' 5 'Friday' 6 'Saturday' 7);
END
TABLE FILE abc
SUM ...
BY ...
ACROSS DAYNO NOPRINT
ACROSS DAYNM AS 'Day'
...
END

Another option is to check (and maybe change) from RS Console / Workspace / Locale / Locale (Language, Numbers, Currency, Dates) / WEEKFIRST parameter. But this will change the day order everywhere
By default it is set to 0 - No setting
From Help content for this setting : "The ISO standard establishes Monday as the first day of the week, so to be fully ISO compliant, the WEEKFIRST parameter should be set to ISO or ISO2."

Another one is to do the below in your code. That way it will affect only that report (see the available Help beside this setting from RS Console for more detail)

 SET WEEKFIRST = <value>

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

September 11, 2019, 11:24 AM

Doug

quote:
Another one is to do the below in your code.

Just a word of caution on this. That is: The users must be aware of the sorting sequence differences between reports, charts, etc., even though it should be obviously apparent ro 'the most casual observer'. We changed it at the system level for a previous client to avoid such confusion.

In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206

September 11, 2019, 03:05 PM

AMC2

Thanks MartinY. I was really wondering if something could be done with the Sort Groups or something within the GUI to make this happen. In any event, I did code it like this in a DEFINE. However, Sunday is not generating on the report. Here's the code:

DEFINE FILE SV_SHIPMENTS ADD
DAY/wr=SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE;
DATE1/MDYY=&DATEMDYY;
DATE2/HMDYYs=HINPUT(14, 'DATE1', 8, 'HMDYYs');
CURR_WEEK/MDYY=DTRUNC(DT_CURRENT_DATE(),WEEK);
CURR_SHIPWEEK/MDYY=DTRUNC( SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE,WEEK);
END
DEFINE FILE SV_SHIPMENTS ADD
DAYNO/I1=DECODE DAY('Sunday' 1 'Monday' 2 'Tuesday' 3 'Wednesday' 4 'Thursday' 5 'Friday' 6 'Saturday' 7);
END
DEFINE FILE SV_SHIPMENTS ADD
6WEEKFILTER/A1=IF SV_SHIPMENTS.SV_SHIPMENTS.SHIPWEEK GE DTADD(DTRUNC('&DATEMDYY',WEEK),WEEK,-6) THEN 'Y' ELSE 'N';
LESSTHANTODAYFILTER/A1=IF SV_SHIPMENTS.SV_SHIPMENTS.SHIPWEEK LE '&DATEMDYY' THEN 'Y' ELSE 'N';
CURR_USER/A100V=UPPER(REPLACE('&IBIMR_user', 'hills\', ''));
END
TABLE FILE SV_SHIPMENTS
SUM
COMPUTE TOTAL_LOADS; AS 'Loads Moved'
BY LOWEST SV_SHIPMENTS.SV_SHIPMENTS.CSR_DEPARTMENT_NAME_ORDER
BY SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE
ACROSS LOWEST SV_SHIPMENTS.SV_SHIPMENTS.DAYNO
ACROSS LOWEST SV_SHIPMENTS.SV_SHIPMENTS.DAY
ON TABLE SUBHEAD
"Daily Truckload Moves Week of"
WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.LOGIN_ID EQ CURR_USER ) AND ( SV_SHIPMENTS.SV_SHIPMENTS.SOURCE_ID EQ 'LME' ) AND ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPMENT_STATUS EQ 'AVAILABLE' OR 'DELIVERED' OR 'PROGRESS' ) AND ( J0.BRANCHES.BRANCH_NAME EQ &BRANCH_NAME.(OR(FIND BRANCHES.BRANCHES.BRANCH_NAME IN branches|FORMAT=A20V)).BRANCH_NAME:. ) AND ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE GE CURR_WEEK );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE ROW-TOTAL AS 'Grand Total'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 11, 2019, 03:22 PM

FP Mod Chuck

AMC2

That probably means you don't have any data for Sunday.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

September 11, 2019, 03:46 PM

AMC2

Chuck - There is data for Sunday. There are some records with SHIPDATES of Sunday.

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 11, 2019, 04:00 PM

FP Mod Chuck

Hmmmm, then I don't know why it would not display.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

September 12, 2019, 07:58 AM

MartinY

AMC2,
Please always use the code tag when posting sample code and/or result
It is the last icon on the ribbon that look like the below

</>

quote:

Chuck - There is data for Sunday. There are some records with SHIPDATES of Sunday.

But are they selected according to your WHERE clause ?

Try with below simplified and cleaned code where you will activate each WHERE clause one by one until you see your Sunday data

DEFINE FILE SV_SHIPMENTS
CURR_WEEK/MDYY=DTRUNC(DT_CURRENT_DATE(),WEEK);
DAY/wr=SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE;
DAYNO/I1=DECODE DAY ('Sunday' 1 'Monday' 2 'Tuesday' 3 'Wednesday' 4 'Thursday' 5 'Friday' 6 'Saturday' 7);
CURR_USER/A100V=UPPER(REPLACE('&IBIMR_user', 'hills\', ''));
END
TABLE FILE SV_SHIPMENTS
SUM COMPUTE TOTAL_LOADS; AS 'Loads Moved'
-*BY LOWEST SV_SHIPMENTS.SV_SHIPMENTS.CSR_DEPARTMENT_NAME_ORDER
BY SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE
BY LOWEST SV_SHIPMENTS.SV_SHIPMENTS.DAYNO
BY LOWEST SV_SHIPMENTS.SV_SHIPMENTS.DAY
ON TABLE SUBHEAD
"Daily Truckload Moves Week of"
WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.LOGIN_ID EQ CURR_USER );
-*WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SOURCE_ID EQ 'LME' );
-*WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPMENT_STATUS EQ 'AVAILABLE' OR 'DELIVERED' OR 'PROGRESS' );
-*WHERE ( J0.BRANCHES.BRANCH_NAME EQ &BRANCH_NAME.(OR(FIND BRANCHES.BRANCHES.BRANCH_NAME IN branches|FORMAT=A20V)).BRANCH_NAME:. );
-*WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE GE CURR_WEEK );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE ROW-TOTAL AS 'Grand Total'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END
-RUN

Since you've included the SHIPDATE (the field used to determine the DAY) as a BY field you should result of having one line per date and the TOTAL_LOADS under the corresponding day of week
Similar as the below code result

DEFINE FILE GGSALES
DT/wr = DATE;
DAY/I2 = DECODE DT ('Sunday' 1 'Monday' 2 'Tuesday' 3 'Wednesday' 4 'Thursday' 5 'Friday' 6 'Saturday' 7);
END
TABLE FILE GGSALES
SUM DOLLARS
BY DATE
ACROSS DAY
ACROSS DT
ON TABLE SET PAGE-NUM NOLEAD
END

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

September 12, 2019, 03:09 PM

AMC2

MartinY - My Sunday data is there until I activate the last clause....which is the filter on SHIPDATE GE CURR_WEEK. It's omitting my 2019/09/08 Sunday record when this particular WHERE filter is included, which it should not be because at this point a SHIPDATE of 2019/09/08 equals the CURR_WEEK date of 2019/09/08. My operator is "GE" for this where filter.

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 12, 2019, 06:00 PM

AMC2

Also, what is also odd is that when I set my operator in the last WHERE filter to an "EQ", I get one record but the SHIPDATE is 2019/09/09 and the CURR_WEEK date is 2019/09/08....but I am setting them equal to each other in the last WHERE. Any more thoughts MartinY?

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 13, 2019, 01:41 AM

Frans

What is your WEEKFIRST setting? It shoul dbe ISO1 in your case.

You can check by putting this before your DEFINE

? SET WEEKFIRST

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

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

September 13, 2019, 05:30 AM

Martin vK

There is another solution you can try:

ACROSS DAY COLUMNS 'Sunday' AND 'Monday' AND 'Tuesday' AND 'Wednesday' AND 'Thursday' AND 'Friday' AND 'Saturday'

Then you can leave out the define and across for DAYNO.
This determines not only the sorting sequence, but it also arranges that all these day columns are always included in the report, whether or not there is data for the specific day. So even if Sunday does not have data due to your where clause, you still get an empty Sunday column, which I think is better when reporting on weekdays.

Nevertheless you should look at your WEEKFIRST setting as Frans suggested to see why the Sunday is not included in your data.

Martin.

This message has been edited. Last edited by: Martin vK,

WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster

September 13, 2019, 07:45 AM

MartinY

quote:
SHIPDATE of 2019/09/08 equals the CURR_WEEK date of 2019/09/08

Also check if they are same format and really contain same data format/type.
E.g. if one is a Date format and the other a DateTime then (numerically talking) 2019/09/08 is not greater or equal to 2019/09/08 12:00:00 because 2019/09/08 is considered having 00:00:00

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

September 13, 2019, 10:48 AM

AMC2

Frans - So my WEEKFIRST setting was 0 here. I changed it and set it to 0 for Sunday. However, I am still not getting the Sunday data. Also, I don't see the WEEKFIRST parameter in the lists of SETs in the GUI. Any thoughts on any of this?

MartinY - both dataes are defined straight dates...YYMD.

Martin vK - I can try your sorting suggestion.

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 13, 2019, 10:53 AM

AMC2

Also, what's odd is that if I change that last WHERE filter to the following condition, it brings back the Sunday data.

WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE GE CURR_WEEK  OR  SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE EQ '20190908');

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 13, 2019, 11:24 AM

MartinY

quote:
Originally posted by AMC2:
Also, what's odd is that if I change that last WHERE filter to the following condition, it brings back the Sunday data.
WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE GE CURR_WEEK  OR  SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE EQ '20190908'); 
 

Seeing that it definitively sounds to me that you don't have same data format between SHIPDATE and CURR_WEEK.
Your CURR_WEEK is defined as MDYY

Try this and look at the displayed dates (SHIPDATE and CURR_WEEK) and show us a sample result

DEFINE FILE SV_SHIPMENTS
CURR_WEEK/MDYY=DTRUNC(DT_CURRENT_DATE(),WEEK);
CURR_USER/A100V=UPPER(REPLACE('&IBIMR_user', 'hills\', ''));
END
TABLE FILE SV_SHIPMENTS

BY SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE
BY CURR_WEEK 

WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.LOGIN_ID EQ CURR_USER );
WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SOURCE_ID EQ 'LME' );
WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPMENT_STATUS EQ 'AVAILABLE' OR 'DELIVERED' OR 'PROGRESS' );
WHERE ( J0.BRANCHES.BRANCH_NAME EQ &BRANCH_NAME.(OR(FIND BRANCHES.BRANCHES.BRANCH_NAME IN branches|FORMAT=A20V)).BRANCH_NAME:. );
WHERE ( SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE GE CURR_WEEK  OR  SV_SHIPMENTS.SV_SHIPMENTS.SHIPDATE EQ '20190908')
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
END
-RUN

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

September 13, 2019, 12:03 PM

AMC2

MartinY - I had caught that earlier and had already changed the CURR_WEEK date to a YYMD format. Here's the output with both dates as a YYMD and the WHERE filter as you had in your last post:

  
Daily Truckload Moves Week of
SHIPDATE	CURR_WEEK	DAYNO	DAY	Loads Moved	Grand Total
2019/09/08	2019/09/08	1	Sunday	2	2.00
2019/09/09	2019/09/08	2	Monday	652	652.00
2019/09/10	2019/09/08	3	Tuesday	611	611.00
2019/09/11	2019/09/08	4	Wednesday	626	626.00
2019/09/12	2019/09/08	5	Thursday	442	442.00
2019/09/13	2019/09/08	6	Friday	76	76.00

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 13, 2019, 12:23 PM

Frans

Pleas echeck the outcome of this code in your env:

 TABLE FILE CAR PRINT COMPUTE FDW/YYMD=DTRUNC(DT_CURRENT_DATE(),WEEK); CAR NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS FDW
END
-RUN
-READFILE FDW
-TYPE FDW = &FDW

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

September 13, 2019, 12:40 PM

AMC2

Frans - I ran this with the CAR file first and it is prompting for FDW and defaults it to FDW...but when executing it from that point it errors indicating it
cannot run with invalid, or missing, values.

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc

September 13, 2019, 01:07 PM

FP Mod Chuck

AMC2

Put this line at the beginning...

-DEFAULTH &FDW='';

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

September 13, 2019, 02:20 PM

AMC2

I get 20190908 as the date value which I should.

  
0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1
 FDW = 20190908

WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc