Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Sorting Sunday thru Saturday
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Sorting Sunday thru Saturday
 Login/Join
 
Gold member
posted
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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.
   Most recent: 8204 Gen 48 in Test and Production.
   Currently Available. Please contact me.
 
Posts: 3040 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1664 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Chuck - There is data for Sunday. There are some records with SHIPDATES of Sunday.


WebFOCUS 8.2.06
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1664 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 411 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 411 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1664 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
Windows
HTML, PDF, Excel, etc
 
Posts: 93 | Registered: August 22, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Sorting Sunday thru Saturday

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.