Db2

I have a sales report that I need to run every week for the previous week. I have a column with the sale date in the table. Since I want to automatically run this report and have it emailed, how do I always filter the report by using today's date minus 7 days?

You can create DEFINE fields (InfoAssist Data tab, Detail (Define), one field named TODAY, format MDYY (or whatever format you need) value is &MDYY, another field WEEKAGO, format MDYY defined as TODAY - 7. Then create a filter for whatever your date field to compare (don't know your field name, so I am just using DATEFIELD) as WHERE DATEFIELD EQUAL TO WEEKAGO.

Thanks David. That was my first approach and I couldn't get it to filter on the field I created. I will try it again and see if I can get it working. Thanks for the information.

You could also just define WEEKAGO (skip the DEFINE for TODAY), as

DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-7), 'YYMD', 'I8YYMD')

You could also just define WEEKAGO (skip the DEFINE for TODAY), as

DATECVT(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-7), 'YYMD', 'I8YYMD')

Another way would be to add 7 days to the date in your table and then compare it to Today constant in the filter.

Create new DEFINE field as DATEADD(date_field, 'D', 7) and then filter on that field being greater or equal to the Today's constant. This translates nicely to SQL.

Everything else I tried seems to require manually editing the fex source.

I was able to replicate what David suggested, but had to put the variable in single quotes '&MDYY'. So the fields ended up being:

TODAY/MDYY='&MDYY';

A_WEEK_AGO/MDYY=DATEADD(TODAY, 'D' , -7) ;