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] Difference between date chosen in prompt and date field, format problem too.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Difference between date chosen in prompt and date field, format problem too.
 Login/Join
 
TRue
Silver Member
posted
I have a 2 part date problem.
I'm trying to define/compute a Length-of-Service field - the difference between a Trader's First Trade date and the last month of the Fiscal Year chosen in the user prompt.

The 2 problems are:
1) Different field formats
In my user prompt the date field - FISCALYEAR - is just an I4 format. And it's Fiscal, not calendar, obviously.
In the report the TradersFirstTrade date field is in YYM format. And it's a calendar date.

2) Calculating the difference between a Calendar date and a Fiscal date.
I believe I need to first convert the FISCALYEAR chosen to the Calendar YYM - eg for 2013 it would be 2013/09. How do I do this? I have tried format conversion formulas, creating a define to say if 2013 is chosen make it 201309, formatted as YYM... No worky!
Then I think I need to Compute the difference between the FISCALYEAR (chosen in the user prompt) and the TradersFirstTrade date field. Is this as simple as using DATEDIF or HDIFF in my compute formula, once the conversion to Calendar is done and the field formats are the same?

Here's a sample of my code so far:
(My LengthService field is returning the difference between first trade date and today's date, currently.)
DEFINE FILE TRADESALESVIEW
NewCust/D5.1=IF TRADESALESVIEW.TRADESALESVIEW.BRANCH_MAIN_BRANCH CONTAINS '-PC' THEN ( TRADESALESVIEW.TRADESALESVIEW.NEWCUSTOMER / 3 ) ELSE TRADESALESVIEW.TRADESALESVIEW.NEWCUSTOMER ;
TradersFirstTrade/YYM=IF TRADESALESVIEW.TRADESALESVIEW.TRADER_NAME EQ 'Trader 1' THEN 200109 
ELSE IF TRADESALESVIEW.TRADESALESVIEW.TRADER_NAME EQ 'Trader 2' OR 'Trader 3' THEN 200202 
ELSE IF TRADESALESVIEW.TRADESALESVIEW.TRADER_NAME EQ 'Trader 4' THEN 200212 
ELSE TRADESALESVIEW.TRADESALESVIEW.TRADER_FT_MONTH ;
ChosenFiscalYYM/YYM=IF &FISCALYEAR EQ '2003' THEN 200309 ELSE IF &FISCALYEAR EQ '2004' THEN 
200409 ELSE IF &FISCALYEAR EQ '2005' THEN 
200509 ELSE IF &FISCALYEAR EQ '2006' THEN 
200609 ELSE IF &FISCALYEAR EQ '2007' THEN 
200709 ELSE IF &FISCALYEAR EQ '2008' THEN 
200809 ELSE IF &FISCALYEAR EQ '2009' THEN 
200909 ELSE IF &FISCALYEAR EQ '2010' THEN 
201009 ELSE IF &FISCALYEAR EQ '2011' THEN 
201109 ELSE IF &FISCALYEAR EQ '2012' THEN 
201209 ELSE IF &FISCALYEAR EQ '2013' THEN 
201309 ELSE IF &FISCALYEAR EQ '2014' THEN 
201409 ELSE IF &FISCALYEAR EQ '2015' THEN 
201509 ELSE IF &FISCALYEAR EQ '2016' THEN 
201609 ELSE IF &FISCALYEAR EQ '2017' THEN 
201709 ELSE 209909;
FiscalYEARMO/I8=TRADESALESVIEW.TRADESALESVIEW.FISCALYEAR ||TRADESALESVIEW.TRADESALESVIEW.FISCALMONTHNO;
TradersFTFiscalYear/YY=FIYR(TradersFirstTrade,'M',10,1,'FYE',TradersFTFiscalYear);
END
TABLE FILE TRADESALESVIEW
SUM TRADESALESVIEW.TRADESALESVIEW.BRANCH_MAIN_BRANCH AS 'Main Branch'
NewCust AS 'New,Customers'
COMPUTE CustomerCount; AS 'Customer,Count'
COMPUTE LengthService/M=DATEDIF ( TRADESALESVIEW.TRADESALESVIEW.TRADER_FT_DATE , &DATEYYMD , 'M' ) ; AS 'Length,of,Service'
COMPUTE ServiceAge/D12.1=LengthService * 5 ; AS 'Service,Age'

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


InfoAssist only - Version: 7703
Windows 7
Excel and PDF outputs
 
Posts: 34 | Registered: August 30, 2012Report This Post
George Patton
Master
posted Hide Post
First of all, your fiscal year appears to end in September for all years, so all the IF THEN ELSE statements seem excessive.

How about something like:
ChosenFiscalYYM/YYM='&FISCALYEAR' || '09';


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
TRue
Silver Member
posted Hide Post
I just tried this, however I get an error:
RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: ChosenFiscalYYM
Perhaps that's because the FISCALYEAR field in the prompt is I4...
I've also attempted to change the FISCALYEAR field in the prompt to be a define with the YY format,
but it always fails too.
Using a defined field for FISCALYEAR prompt also means I lose the dynamic filtering;
I would have to specify the years which are allowed to be chosen instead. Hence my attempt
with all the IF THEN ELSE statements to steal the integer value from the user prompt
and turn it into YYM format.
(P.S. your example still helps me to understand the usage of apostrophe's in concatenation so thank you)


InfoAssist only - Version: 7703
Windows 7
Excel and PDF outputs
 
Posts: 34 | Registered: August 30, 2012Report This Post
Tom Flynn
Expert
posted Hide Post
 
ChosenFiscal01/A6    = '&FISCALYEAR.EVAL' || '09'; 
ChosenFiscal02/I6YYM = EDIT(ChosenFiscal01);
ChosenFiscalYYM/YYM  = ChosenFiscal02;


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
George Patton
Master
posted Hide Post
Thanks Tom,

I did look very quickly at 1001 ways to work with dates in WebFocus very briefly (p. 167) and got the impression that an alpha could convert directly to a date (even though I've never done it that way).


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
TRue
Silver Member
posted Hide Post
I was able to make Tom's formulas work. Learned a lot in the last couple hours, too.

I had to change the name of my ChosenFiscalYYM define, since for some reason I find InfoAssist refusing to recognize updated formulas in old fields sometimes.
I also had to change the formatting to go down to the day, being as I needed to use the DATEDIF formula for determining Length of Service and it needs full component dates.
(Even though I'm calculating how many months have elapsed. *Eyeroll*)
In the end this is what works:
ChosenFiscal01/A8='&FISCALYEAR.EVAL' || '09' ||'30';
ChosenFiscal02/I8YYMD=EDIT ( ChosenFiscal01 ) ;
ChosenFiscalYrMo/YYM=ChosenFiscal02;
ChosenFiscalYYMD/YYMD=ChosenFiscal02;

COMPUTE LengthService/I8=DATEDIF ( TRADESALESVIEW.TRADESALESVIEW.TRADER_FT_DATE , ChosenFiscalYYMD , 'M' ); AS 'Length,of,Service'


Thank you for your formulas, Tom.


InfoAssist only - Version: 7703
Windows 7
Excel and PDF outputs
 
Posts: 34 | Registered: August 30, 2012Report 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] Difference between date chosen in prompt and date field, format problem too.

Copyright © 1996-2020 Information Builders