Hello,

I have been trying unsuccessfully for a few days now, to use a detail define field in a filter and get it to work. The need is to be able to apply a filter to look back 37 months (yes, 37!) in a data-set to only return those records with a date of service > 37 months ago. I actually got a WF developer (on our staff) to assist, but her suggestion didn't work either. I am thinking I may be looking at this the wrong way, so any assistance is appreciated.

To be as clear as I can...
I used the DATEADD function and it did work to bring back the date 37 months prior to the service date, so that part is ok. I can't get it to work as a filter...
Where Service Date > 37mos.

In the above case, the 37mos field is defined as: DATEADD(REMITTANCE.Remit_Recon.Date_Of_Service,'M', -37)

I've also tried making a filter as an expression: REMITTANCE.Remit_Recon.Date_Of_Service GT DATEADD ( REMITTANCE.Remit_Recon.Date_Of_Service , 'M', -37)
but that didn't work either. I still receive all records in the file instead of just the ones I want. I know I could make a simple filter with the Date of Service > any date I enter, but the users don't want to have to do that. They want to be able to just look at anything from 37 months ago on the date they click Run.

Am I looking at this totally the wrong way?

Thank you.

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

What's the DBMS?
What's the format of the date field in question?
Can you show us your code example?

The DBMS is Greenplum.
The date format is MDYY.
I am using InfoAssist. Is this what you want:
-*COMPONENT=Define_REMITTANCE
DEFINE FILE REMITTANCE ADD
today/A20=TODAY ( 'A10' ) ;
37monthsago1/MDYY=DATEADD ( &DATEMDYY , 'M' , -37 ) ;
37monthsago/MDYY=DATEADD ( REMITTANCE.Remit_Recon.Date_Of_Service , 'M' , -37 ) ;
servicedate/A20=DATECVT ( REMITTANCE.Remit_Recon.Date_Of_Service , 'MDYY' , 'A10' ) ;
37mos/MDYY=DATEADD ( REMITTANCE.Remit_Recon.Date_Of_Service , 'M' , -37 ) ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE REMITTANCE
-SET &SECTION='WHERE';
-INCLUDE IBFS:/WFC/Repository/Business_Views/Remittance.fex
BY REMITTANCE.Remit_Recon.Office_Acct_No
BY REMITTANCE.Remit_Recon.Provider
BY REMITTANCE.Remit_Recon.Remit_Locator_Code
BY REMITTANCE.Remit_Recon.Remit_Locator_County_Code
BY REMITTANCE.Remit_Recon.Remit_Locator_County_Name
BY REMITTANCE.Remit_Recon.Remit_Locator_Region_Name
BY REMITTANCE.Remit_Recon.Remit_Date
BY REMITTANCE.Remit_Recon.Date_Of_Service
WHERE REMITTANCE.Remit_Recon.Status IN ( 'PAID' , 'VOID' );
WHERE REMITTANCE.Remit_Recon.Date_Of_Service GT DATEADD ( REMITTANCE.Remit_Recon.Date_Of_Service , 'M', -37);
WHERE REMITTANCE.Remit_Recon.Remit_Rate_Code IN ( '3478' , '3479' , '3480' );
ON TABLE PCHOLD FORMAT EXL07
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=OFF, $
ENDSTYLE
END

-* Begin MR Suffix -*
-SET &SECTION='SUFFIX';
-INCLUDE IBFS:/WFC/Repository/Business_Views/Remittance.fex
-* END MR Suffix -*
-RUN

I created something off of the SQL Server Orders table below but it should work on GP as well. You should define the comparison field and use the field not the function on the WHERE test.

DEFINE FILE ORDERS
TOD/YYMD=&YYMD;
TOD_37/HYYMD=DATEADD(TOD,'M',-37);
END

TABLE FILE ORDERS
BY ORDERDATE
BY SHIPNAME
BY TOD_37
WHERE ORDERDATE LT TOD_37;
ON TABLE PCHOLD FORMAT HTML
END
  

Pay attention on fact that Babak is using a YMD date which is "logically" ordered not a MDY which is not.

Consider the following dates in MDY

09012017 - current calendar month (as of 1 day of month)
09012016 - 12 months ago
09012015 - 24 months ago
09012014 - 36 months ago
08012014 - 37 months ago
09022012
09032013
09042014
01012017
02012017


If I do order the above from the lowest to the greatest is giving me :

01012017
02012017
08012014
09012014
09012015
09012016
09012017
09022012
09032013
09042014

Then, from your requirement you want all records that are greater than 37 months ago. In programing words (using my sample dates) : Date GT 08012014
This will result in selecting :

09012014
09012015
09012016
09012017
09022012
09032013
09042014

Which is wrong. 09022012 and 09032013 are not greater than August 1st 2014 and where are records from January and February 2017 ?

You have to pay attention when filtering from dates. I suggest to always use the logical format YMD or YYMD and compare with a date in the same format. Specifically when using a range.
I understand that MDY is the "natural" speaking/display English format but it is not giving a proper logical (time base) order.

Thank you, Martin (and Babak)... I understand now why I am seeing everything. Since the data is in the MDYY format, I will first need to edit it to YYMD to get it to work. I will give this a try and let you know what happens.

Suggestion: Always make sure your signature reflects the version of the product you are actually using. Alternatively, make sure you tell us what version you're dealing with.

Well, my first try failed, but I think I may know why. I'll keep trying.

Babak - your code is for App_Studio, yes? I am using INFOASSIST.

I hand coded it but you can also paint it in InfoAssist. It'll look like this:

 
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
SET HTMLENCODE=ON
SET HTMLCSS=ON
SET EMPTYREPORT=ON

-*COMPONENT=Define_orders
DEFINE FILE ibisamp/orders
 TOD/YYMD=&YYMD;
 TOD_37/HYYMD=DATEADD(TOD,'M',-37);
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE ibisamp/orders
BY ORDERS.ORDERS.ORDERDATE
BY ORDERS.ORDERS.SHIPNAME
BY TOD_37
WHERE ORDERS.ORDERS.ORDERDATE LT TOD_37;
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, $
ENDSTYLE
END

-RUN