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] &YYMD not showing up

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] &YYMD not showing up
 Login/Join
 
Gold member
posted
I am just trying to make a date which is 30 days before Today .. but nothing is showing for TodayDT (TodayDT/YYMD=&YYMD), and thus the 30 days before date is also a dot....ideas?

-*COMPONENT=Join_J002
JOIN LEFT_OUTER IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.VISITID IN IBI_VIEW_PT_SUMMARY2
TO MULTIPLE IBI_TABLE_THIRTY_READMIT.THIRTY_READMIT.ORIG_VID IN IBI_TABLE_THIRTY_READMIT TAG J002 AS J002
END
-*COMPONENT=Join_J003
JOIN J002.THIRTY_READMIT.ORIG_VID IN IBI_VIEW_PT_SUMMARY2
TO MULTIPLE IBI_TABLE_VISIT.VISIT.VISITID IN IBI_TABLE_VISIT TAG J003 AS J003
END
-*COMPONENT=Define_IBI_VIEW_PT_SUMMARY2
DEFINE FILE IBI_VIEW_PT_SUMMARY2
TodayDT/YYMD=&YYMD;
OneMO/YYMD = DATeDIFF(TodayDT, 'D', -30)
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE IBI_VIEW_PT_SUMMARY2
SUM COMPUTE todssys/YYMD=DATECVT ( &YYMD , 'I8YYMD' , 'YYMD' ) ;
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.ACCT_NUM
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.LST_NM
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.FST_NM
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DIAGNOSIS
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.LOCATION_ID
BY TodayDT
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.ADMIT_DT
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DISCHARGE_DT
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DISCHARGEDISPOSITIONID
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.VST_REASON
BY J003.VISIT.DISCHARGE_DT AS '30 Day Prior Discharge Date'
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.LOS
WHERE IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DIAGNOSIS EQ '433.01' OR '433.10' OR '433.11' OR '433.21' OR '433.31' OR '433.81' OR '433.91' OR '434.00' OR '434.01' OR '434.11' OR '434.91' OR '436' OR '430' OR '431';
WHERE IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.PATIENTCLASS_ID EQ 'IN' OR 'INO' OR 'ER';
WHERE IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DISCHARGE_DT GE '&MINDISCHARGE_DT.Report Start Date: .';
WHERE IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DISCHARGE_DT LE '&MAXDISCHARGE_DT.Report End Dt YYYY-MM-DD:.';
ON TABLE SUBHEAD
" Stroke Details - Discharged Patients"

This message has been edited. Last edited by: <Kathryn Henning>,


8002 Windows
 
Posts: 50 | Registered: May 21, 2012Report This Post
Expert
posted Hide Post
DATeDIFF is not a function. WebFOCUS is very case-sensitive - all function names are in uppercase. DATEDIF is a function to calculate the difference between two dates.

Use DATEADD to add or subtract a date unit from a date. Example:

DEFINE FILE CAR
TodayDT/YYMD=&YYMD;
OneMO/YYMD = DATEADD(TodayDT, 'DAY', -30);
END

TABLE FILE CAR
SUM 
TodayDT
OneMO
SALES
BY COUNTRY
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Hi Francis thanks for the response.. &YYMD is not working .. so I can't even get to a DATEADD. If I use &YYMD as a Define it does not show anything just a dot in the results set. If I use it as a Summary calc it gives me todays date, but then I cannot use it in a DATEADD.


8002 Windows
 
Posts: 50 | Registered: May 21, 2012Report This Post
Expert
posted Hide Post
Put quotes around &YYMD:
 DEFINE FILE CAR
TodayDT/YYMD='&YYMD';
OneMO/YYMD = DATEADD(TodayDT, 'DAY', -30);
END

TABLE FILE CAR
SUM 
TodayDT
OneMO
SALES
BY COUNTRY
END


Generates:
  
PAGE 1 
 
COUNTRY   TodayDT    OneMO      SALES 
ENGLAND   2014/05/21 2014/04/21 12000 
FRANCE    2014/05/21 2014/04/21 0 
ITALY     2014/05/21 2014/04/21 30200 
JAPAN     2014/05/21 2014/04/21 78030 
W GERMANY 2014/05/21 2014/04/21 88190 



Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
The code I provided works in WebFOCUS 7.7.05.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
quote:
The code I provided works in WebFOCUS 7.7.05.

And in 8.006.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
I added the single quotes, but still just a dot . And the Summary Calc shows fine .. this is my current code .

-*COMPONENT=Join_J002
JOIN LEFT_OUTER IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.VISITID IN IBI_VIEW_PT_SUMMARY2
TO MULTIPLE IBI_TABLE_THIRTY_READMIT.THIRTY_READMIT.ORIG_VID IN IBI_TABLE_THIRTY_READMIT TAG J002 AS J002
END
-*COMPONENT=Join_J003
JOIN J002.THIRTY_READMIT.ORIG_VID IN IBI_VIEW_PT_SUMMARY2
TO MULTIPLE IBI_TABLE_VISIT.VISIT.VISITID IN IBI_TABLE_VISIT TAG J003 AS J003
END
-*COMPONENT=Define_IBI_VIEW_PT_SUMMARY2
DEFINE FILE IBI_VIEW_PT_SUMMARY2
TDAY/YYMD='&YYMD' ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE IBI_VIEW_PT_SUMMARY2
SUM COMPUTE SUMMTDAY/YYMD='&YYMD';
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.ACCT_NUM
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.LST_NM
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.FST_NM
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DIAGNOSIS
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.LOCATION_ID
BY TDAY
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.ADMIT_DT
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DISCHARGE_DT
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.DISCHARGEDISPOSITIONID
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.VST_REASON
BY J003.VISIT.DISCHARGE_DT AS '30 Day Prior Discharge Date'
BY IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.LOS


8002 Windows
 
Posts: 50 | Registered: May 21, 2012Report This Post
Expert
posted Hide Post
1) I don't know what's going wrong in your code, other than perhaps it's something to do with the left outer join and there being no data in one of the tables.

2) Why do you feel "but then I cannot use it in a DATEADD"?

This works:

TABLE FILE CAR
SUM 
COMPUTE TodayDT/YYMD = &YYMD;
COMPUTE OneMO/YYMD   = DATEADD(TodayDT, 'DAY', -30);
SALES
BY COUNTRY
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
Tara,

Can you simplify your fex? Since you believe it to be an issue with the &YYMD variable, could you try running Francis' example? If that works you know your issue is elsewhere. Also you could use the command
  -? &  
. This will print out all of the variables and you can see whether &YYMD is in that list, or if not what variables are available to you.

Also, for your define field of TDAY, you may need to use the with command

 TDAY/YYMD WITH IBI_VIEW_PT_SUMMARY2.PATIENT_SUMMARY2_VW.ACCT_NUM='&YYMD' ; 


Since the define field has no table to refer to, WebFOCUS might have an issue with it and would require you to associate it to one of your tables.

Thanks!


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Gold member
posted Hide Post
Thanks guys. I deleted all of the define and calcs and then just made a define with DATEADD ('&YYMD', 'D', -30) and named it something totally different , and I have the date I wanted.. I did check with -? & and it is in my list ( neat by the way). So I am not sure why '&YYMD' does not work independantly but my end objective is met.


8002 Windows
 
Posts: 50 | Registered: May 21, 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] &YYMD not showing up

Copyright © 1996-2020 Information Builders