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     [SOLVED] looping through quarters... anyone???

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] looping through quarters... anyone???
 Login/Join
 
Silver Member
posted
Anyone ever have to loop through quarters? ...just when I thought I figured out the whole looping through months thing... I now have a new challenge!

I'm starting at the beginning of 2008 and need to show (and calculate some things) all of the quarters until today.

Any help would be greatly appreciated!

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


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
Just a thought: You could loop through the months (seeing that you have aleray conquered that one) and increment the quarters for every forth month after resetting the current years quarter to 1 at the turn of a year.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
Are you certain you need a dialog manager loop? You can DEFINE the quarter (/YYQ) based on any date field, and use that to sort and summarize your data by quarter in a single TABLE request, for dates falling within the required range.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
Good Question Jack! I "assumed" DM because of the reference in the initial post... But, if it's in a DEFINE / TABLE FILE request. Then, by all means the Quarter Option is Great.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Silver Member
posted Hide Post
I have to set the end of the quarter for each iteration to determine if a "ticket" (such as a help desk ticket) was opened before the end of the quarter but closed after the end of the quarter. It's a calculation based on tickets that could now be closed.


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Virtuoso
posted Hide Post
So you've got opened and closed dates on each ticket. Define a field for open and closed quarter and then compare them.
OPEN_QTR/YYQ=TICKET_OPEN_DT;
CLOSED_QTR/YYQ=TICKET_CLOSED_DT;
CLOSED_IN_QTR/A7=IF OPEN_QTR LT CLOSED_QTR THEN 'Previous' ELSE IF OPEN_QTR EQ CLOSED_QTR THEN 'Current' ELSE 'Error';

That's probably your best solution. You already have the dates you need on the records so why mess with &variables?

You can certainly do it in Dialogue Manager loops the same way as you would a month adding 3 to the month in each loop and increment year after each 4 loops. Then use EOQ to move each date to the end of quarter date.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Silver Member
posted Hide Post
Thanks for all of the suggestions! In the suggestion to set the opened and closed dates and then determine the "CLOSED_IN_QTR" value, I'm not sure I understand how I'd know which quarter the ticket was still "open" for.

In the suggestion to loop through the months and add 3 months, I think I'd need to move the new date to the end of the month. Does that sound correct?


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
quote:
In the suggestion to loop through the months and add 3 months ... Does that sound correct?
~~~ Yes it sounds correct... It's definitely a good starting point...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
quote:
I'm not sure I understand how I'd know which quarter the ticket was still "open" for.

Any ticket where OPEN_QTR is less than CLOSED_QTR would still be open for the quarter being tested. When they are equal, that would mean that the ticket had been closed during the current quarter being tested (so would not be open at the end of quarter) and it should probably be an error if the open quarter comes after the closed quarter.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Silver Member
posted Hide Post
Oh, ok...then I'd still need to loop through the quarters then, correct? ...to define which quarter is used in the test?


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Virtuoso
posted Hide Post
Depends on how you are running the report. Where is it getting the parameters from? You only need a loop (meaning a Dialogue Manager loop) if you have to re-execute a section of code multiple times, changing parameters values each time. I've not seen anything in the info you've provided that indicates that DM is required. Maybe a little more info on how you want the process to run?

If the looping month thing has previously solved your problems, then do as Doug originally suggested and just increment the months 3 at a time instead of one at a time.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Silver Member
posted Hide Post
This is what I need to calculate...
quote:
I have to set the end of the quarter for each iteration to determine if a "ticket" (such as a help desk ticket) was opened before the end of the quarter but closed after the end of the quarter. It's a calculation based on tickets that could now be closed.


I'm going to try what Doug suggested but I'm not sure how to do it. I understand the concept. Coding it that way is where I'm stuck. I really need some DATE training. Any suggestions of a good course?

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


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Expert
posted Hide Post
A good place to start may be the (Almost) 1001 Ways to Work With Dates in WebFOCUS document. You can buy the complete manual which comes with a handy CD full of routines... Enjoy...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Silver Member
posted Hide Post
Thanks Doug! I'll check that out.

Here's how I ended up getting it to work:

-SET &CNT = 1;
-SET &CNT2 = 2;
-SET &BEG_DATE=2008|01|01;
-SET &SYSDATE = TODAY(A10);
-SET &MO = EDIT(&SYSDATE,'99$$$$$$$$');
-SET &DY = EDIT(&SYSDATE,'$$$99$$$$$');
-SET &YR = EDIT(&SYSDATE,'$$$$$$9999');
-SET &STPDATE = &YR||&MO||&DY;
-SET &STP_QTR = DATECVT((DATEMOV((DATECVT (&STPDATE, 'I8YYMD', 'YYMD')),'EOQ')),'YYMD','I8YYMD');
-* SET BEG DATE TO END OF QUARTER
-SET &END_QTR = DATECVT((DATEMOV((DATECVT (&BEG_DATE, 'I8YYMD', 'YYMD')),'EOQ')),'YYMD','I8YYMD');

-REPEAT LOOP1 WHILE &END_QTR LE &STP_QTR;

DEFINE FILE FILE_NAME
FDATE/YYMD=&END_QTR;
END

TABLE FILE FILE_NAME
PRINT ID OPENED_DATE CLOSED_DATE
WHERE OPENED_DATE LE FDATE AND ((CLOSED_DATE EQ '') OR (CLOSED_DATE EQ MISSING) OR (CLOSED_DATE GT FDATE));
ON TABLE HOLD AS TEST1
END

TABLE FILE TEST1
SUM CNT.ID
BY FDATE
ON TABLE HOLD AS TEST2&CNT FORMAT ALPHA
END

-SET &NBMO = 70;
-SET &END_R1=AYMD(&END_QTR,&NBMO,'I8YYMD');
-SET &END_QTR = DATECVT((DATEMOV((DATECVT (&END_R1, 'I8YYMD', 'YYMD')),'EOQ')),'YYMD','I8YYMD');
-SET &CNT = &CNT + 1;
-LOOP1

-SET &CNT=&CNT-1;
TABLE FILE TEST21
PRINT *
ON TABLE HOLD AS TEST_END FORMAT ALPHA
-REPEAT LOOP2 WHILE &CNT2 LE &CNT;
MORE
FILE TEST2&CNT2
-SET &CNT2 = &CNT2 + 1;
-LOOP2
END

Thanks for your help!


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report 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     [SOLVED] looping through quarters... anyone???

Copyright © 1996-2020 Information Builders