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] Quarter Begin and End dates for filters

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Quarter Begin and End dates for filters
 Login/Join
 
Platinum Member
posted
Does anyone know of a way of getting the beginning and ending dates of quarter ranges, given that you have the starting and ending dates of each quarter. I can do it easily if one quarter is selected the tricky part is when more than one is selected. For instance the user selects to Q1, Q3 and Q4? Should I just have four filters or is there a more efficient way of doing it?

Thanks

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


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Silver Member
posted Hide Post
Hi Trudy,

Not certain of your setup with who can access validation tables, but FTVFSPD has a listing of fiscal periods along with their start & end dates. You could probably write a define to obtain the info (Q1 = FSPD 01 Start Date & FSPD 03 End date) you want then use a -READFILE to create params off them then -Include the procedure in your report.


WF Version 8105
 
Posts: 45 | Registered: October 07, 2015Report This Post
Virtuoso
posted Hide Post
As per your example (selected Q1, Q3 and Q4) you will have to test three date range which is, according to me, the simplest way.
Assuming "normal" civil quarter :
1- Jan 1 To March 31st
2- July 1st To Sept 30th
3- Oct 1st To Dec 31st

So you cannot use a single start / end date test since no date should be selected between May 1st and June 30th.

Does your user just select a quarter (Q1, Q2, Q3 or Q4) or must also have to select a year (which I think it should and gives more flexibility) ?

How you quarter selection is displayed and come from where (static/dynamic drop list, radio, ...) ?

What I would do is
1- have a dynamic drop list with the available years
2- have a static multi drop list or radio or check box with 4 options : a- Q1, b- Q2, c- Q3, d- Q4
3- based on selection made built your date range (start/end) by quarter

But this is one of the multiple options that can came up from other people and since I don't know all your request, it may not be suitable.


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
Master
posted Hide Post
How's this? ( easy maintenance / low-code solution )

-PROMPT &Q1.(,).Q1;
-PROMPT &Q2.(,).Q2;
-PROMPT &Q3.(,).Q3;
-PROMPT &Q4.(,).Q4;

TABLE FILE data
...
WHERE
( DATE GE q1_start AND DATE LE q1_end AND '&Q1' EQ 'yes' )
OR
( DATE GE q2_start AND DATE LE q2_end AND '&Q2' EQ 'yes' )
OR
( DATE GE q3_start AND DATE LE q3_end AND '&Q3' EQ 'yes' )
OR
( DATE GE q4_start AND DATE LE q4_end AND '&Q4' EQ 'yes' )
END


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Member
posted Hide Post
-SET &&LDATE = 01011900;
-IF &&CHGDATE IS 'Y' GOTO RPTDATE;
-SET &TIMENOW = HHMMSS ('A8');
-SET &CHCKHH = EDIT (&TIMENOW,'99$$$$$$');
-*
-RPTDATE
-*
-SET &DAY1 = EDIT(&YMD,'$$$$99');
-SET &MON1 = EDIT(&YMD,'$$99$$');
-SET &YER1 = EDIT(&YMD,'99$$$$');
-SET &CNT1 = IF &YER1 LT '90' THEN '20' ELSE '19';
-SET &FULLYR = &CNT1 | &YER1;
-SET &LASTYR = (&FULLYR - 1);
-SET &ALASTYR = EDIT(&LASTYR,'$$99');
-SET &LASTMO = DECODE &MON1 ('01' '12' '02' '01' '03' '02'
- '04' '03' '05' '04' '06' '05'
- '07' '06' '08' '07' '09' '08'
- '10' '09' '11' '10' '12' '11');
-SET &LASTMO2= DECODE &MON1 ('01' '11' '02' '12' '03' '01'
-SET &LASTYR2=&LASTYR - 1;
-SET &&CYBEG = '0101' | &FULLYR;
-SET &&PYBEG = '0101' | &LASTYR;
-SET &&PYEND = '1231' | &LASTYR;
-SET &&PYEND2 = '1231' | &LASTYR2;
-SET &&CFYBEG = '1001' | &LASTYR;
-SET &&CFYEND = '0930' | &FULLYR;
-SET &&PFYBEG = '1001' | &LASTYR2;
-SET &&PFYEND = '0930' | &LASTYR;
-SET &&CFY = &FULLYR;
-SET &&PFY = &LASTYR;
-SET &&NXFY = &NXTYR;
-SET &&NXFYBEG = '1001' | &FULLYR;
-SET &&NXFYEND = '0930' | &NXTYR;
-*
-SET &&NEXTYR= &MON1 | &DAY1NX | &NXTYR;
-SET &&NEXTYR5= &MON1 | &DAY1NX5 | &NXTYR5;
-SET &&NEXTYR10= &MON1 | &DAY1NX10 | &NXTYR10;
-***
-*
-SET &&CQTR = DECODE &MON1 ('01' '01' '02' '01' '03' '01'
- '04' '02' '05' '02' '06' '02'
- '07' '03' '08' '03' '09' '03'
- '10' '04' '11' '04' '12' '04');
-SET &CQBEG1 = DECODE &&CQTR ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &&CQBEG = &CQBEG1 | &FULLYR;
-SET &CQEND1 = DECODE &&CQTR ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &&CQEND = &CQEND1 | &FULLYR;

-*
-SET &&PQTR = DECODE &MON1 ('01' '04' '02' '04' '03' '04'
- '04' '01' '05' '01' '06' '01'
- '07' '02' '08' '02' '09' '02'
- '10' '03' '11' '03' '12' '03');
-SET &PQBEG1 = DECODE &&PQTR ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND1 = DECODE &&PQTR ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR = IF &&PQTR IS '04' THEN &LASTYR ELSE &FULLYR;
-SET &&PQBEG = &PQBEG1 | &PREVYR;
-SET &&PQEND = &PQEND1 | &PREVYR;
-*
-*
-SET &&PQTR2 = DECODE &MON1 ('01' '03' '02' '03' '03' '03'
- '04' '04' '05' '04' '06' '04'
- '07' '01' '08' '01' '09' '01'
- '10' '02' '11' '02' '12' '02');
-SET &PQBEG2A= DECODE &&PQTR2 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND2A= DECODE &&PQTR2 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR2 = IF &&PQTR2 IS '04' THEN &LASTYR ELSE
- IF &&PQTR IS '04' THEN &LASTYR ELSE
- &FULLYR;
-SET &&PQBEG2 = &PQBEG2A | &PREVYR2;
-SET &&PQEND2 = &PQEND2A | &PREVYR2;
-*
-*
-*
-SET &&PQTR3 = DECODE &MON1 ('01' '02' '02' '02' '03' '02'
- '04' '03' '05' '03' '06' '03'
- '07' '04' '08' '04' '09' '04'
- '10' '01' '11' '01' '12' '01');
-SET &PQBEG3A = DECODE &&PQTR3 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND3A = DECODE &&PQTR3 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR3 = IF &&PQTR3 IS '04' THEN &LASTYR ELSE
- IF &&PQTR2 IS '04' THEN &LASTYR ELSE
- IF &&PQTR IS '04' THEN &LASTYR ELSE
- &FULLYR;
-SET &&PQBEG3 = &PQBEG3A | &PREVYR3;
-SET &&PQEND3 = &PQEND3A | &PREVYR3;
-*
-*
-SET &&PQTR4 = DECODE &MON1 ('01' '01' '02' '01' '03' '01'
- '04' '02' '05' '02' '06' '02'
- '07' '03' '08' '03' '09' '03'
- '10' '04' '11' '04' '12' '04');
-SET &PQBEG4A = DECODE &&PQTR4 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND4A = DECODE &&PQTR4 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR4 = IF &&PQTR4 IS '04' THEN &LASTYR ELSE
- IF &&PQTR3 IS '04' THEN &LASTYR ELSE
- IF &&PQTR2 IS '04' THEN &LASTYR ELSE
- IF &&PQTR IS '04' THEN &LASTYR ELSE
- &FULLYR;
-SET &&PQBEG4 = &PQBEG4A | &PREVYR4;
-SET &&PQEND4 = &PQEND4A | &PREVYR4;
-*
-*
-*
-SET &&PQTR5 = DECODE &MON1 ('01' '04' '02' '04' '03' '04'
- '04' '01' '05' '01' '06' '01'
- '07' '02' '08' '02' '09' '02'
- '10' '03' '11' '03' '12' '03');
-SET &PQBEG5A = DECODE &&PQTR5 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND5A = DECODE &&PQTR5 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR5 = IF &&PQTR5 IS '04' THEN &LASTYR2 ELSE &LASTYR;
-SET &&PQBEG5 = &PQBEG5A | &PREVYR5;
-SET &&PQEND5 = &PQEND5A | &PREVYR5;
-*
-*
-*


WebFOCUS 8
Windows, All Outputs
 
Posts: 1 | Registered: June 13, 2017Report This Post
Virtuoso
posted Hide Post
Nice complicated code lolu Sweating Smiler , but this is specific for your case. And it seems that your fiscal year starts on Oct 1st.

Things that I denoted :
1- If you would had used &YYMD instead of &YMD you shouldn't have to bother about century assignation.
2- IBI functions do exist to subtract years, months and days from a date. So you may not need to split in each element (DD, MM, YY) and re-construct the dates as you did.
3- Your sample as is will not work since some variable are not defined such as &DAY1NX and &NXTYR, ...


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
Silver Member
posted Hide Post
quote:
Originally posted by Dave:
How's this? ( easy maintenance / low-code solution )

-PROMPT &Q1.(,).Q1;
-PROMPT &Q2.(,).Q2;
-PROMPT &Q3.(,).Q3;
-PROMPT &Q4.(,).Q4;

TABLE FILE data
...
WHERE
( DATE GE q1_start AND DATE LE q1_end AND '&Q1' EQ 'yes' )
OR
( DATE GE q2_start AND DATE LE q2_end AND '&Q2' EQ 'yes' )
OR
( DATE GE q3_start AND DATE LE q3_end AND '&Q3' EQ 'yes' )
OR
( DATE GE q4_start AND DATE LE q4_end AND '&Q4' EQ 'yes' )
END


Dave,

That's a great solution.

As a test I utilized that validation table I had mentioned in my earlier post, created a procedure with a -READFILE to capture the quarter start and end dates as parameters (based on a parameter where the user is prompted to enter the Fiscal Year). I then used a -INCLUDE to bring in those values into my new procedure, added your -PROMPTS:

  
-PROMPT &Q1.(YES,NO).Q1;
-PROMPT &Q2.(YES,NO).Q2;
-PROMPT &Q3.(YES,NO).Q3;
-PROMPT &Q4.(YES,NO).Q4;


then added my TABLE file & other criteria along with the OR's for the Quarters in the WHERE statement:

( DATE_FIELD GE &Q1_START AND DATE LE &Q1_END AND '&Q1' EQ 'YES' )
OR
( DATE_FIELD GE &Q2_START AND DATE LE &Q2_END AND '&Q2' EQ 'YES' )
OR
( DATE_FIELD GE &Q3_START AND DATE LE &Q3_END AND '&Q3' EQ 'YES' )
OR
( DATE_FIELD GE &Q4_START AND DATE LE &Q4_END AND '&Q4' EQ 'YES' )


In this case, the user is prompted for only the Fiscal year and to select which quarters they desire. This does make for a pretty dynamic/easy to maintain solution. I'll forward my code to Trudy as we utilize the same DB and Fiscal Year schedule.


WF Version 8105
 
Posts: 45 | Registered: October 07, 2015Report This Post
Virtuoso
posted Hide Post
Hi Lolu

Welcome to Focal Point.. I love that your first post is trying to help someone else! This forum will be a great place for you! Keep it up!

Thanks,


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
Master
posted Hide Post
Glad to be of assistence :-)

I use "fake" WHERE statements for all kind of stuff :-)
also to disable or enable stuff, sometime a life-saver.

WHERE
 YEAR EQ &PARAM AND &PARAMTYPE EQ 'year'
OR
 WEEK EQ &PARAM AND &PARAMTYPE EQ 'week'


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report 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] Quarter Begin and End dates for filters

Copyright © 1996-2020 Information Builders