Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Sorting Sunday thru Saturday
 Login/Join
 
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Platinum Member
posted Hide Post
Chuck - There is data for Sunday. There are some records with SHIPDATES of Sunday.


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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: 2127 | Location: Customer Support | Registered: April 12, 2005Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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: 454 | Location: Europe | Registered: February 05, 2007Report 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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report 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: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Platinum 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
SQL Server
HTML, PDF, Excel, etc
 
Posts: 168 | Registered: August 22, 2019Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders