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,

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.

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.

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.

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.

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.

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?

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...

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.

Oh, ok...then I'd still need to loop through the quarters then, correct? ...to define which quarter is used in the test?

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.

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,

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...

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!