It's my first day with InfoAssist+ and I've already found a pretty cool new feature, when filtering by a simple date it pulls up a few predetermined options:



I was wondering if anyone knows if you can define these somewhere? What I'm really looking for is "Beginning of Last Week", "Beginning of Last Month", "Beginning of Last Quarter", ETC.

Ever since I first started using InfoAssist I've fought with automation in date filtering, I have to jump through so many hoops to filter for something like "Last Week" and it involves joins and define fields which ultimately heed long run times. If only IA had the -Get feature but it doesn't, I appreciate any help.

Thanks.

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

You can create your own global variables that calculate these dates and use them as a parameter.

-SET &&CURDATE=&YYMD;
 
-* This sets what Yesterday is:
-SET &&YESTERDAY=AYMD(&&CURDATE,-1,'I8YYMD');
 
-* This sets Current Year based upon Yesterday
-SET &&CYEAR=EDIT(&&YESTERDAY,'9999');
 
-* This sets Prior Year based upon Yesterday
-SET &&PYEAR=&&CYEAR-1;
 
-* This sets what the current month is based upon todays's date
-SET &&CMONTH=EDIT(&&CURDATE,'999999') || '01';
-SET &CMONTHYYM=EDIT(&&CURDATE,'999999');
 
-* This sets what last month is based upon today's date
-SET &&PMONTH=EDIT(AYM(&CMONTHYYM, -1, 'I6YMD')) || '01';
-SET &PMONTHYYM=EDIT(&&PMONTH,'999999');
 
-* This sets 7 days prior based upon &YESTERDAY
-SET &&P7DAY=AYMD(&&YESTERDAY,-7,'I8YYMD');

I usually run these from my edasprof so they're always available. Assuming you put these commands in baseapp\date_settings this is what you should add to your server profile:

-INCLUDE date_settings

Now, to use any of these variables you'll have to DEFINE a field and set it to the variable you want:

  
DEFINE FILE wfretail82/wf_retail_tiny
 YES_DAY/YYMD=&&YESTERDAY;
END

TABLE FILE ...
.
.
WHERE TIME_DATE_DAY_COMPONENT EQ YES_DAY;
END

So you're now comparing the date field in your database with a DEFINEd field you populated with the value of yesterday.

Cimmerian

To expand what you can do with Babak's suggestion. Once you have that date_settings procedure in place you can also use the global variables in the master file and set up FILTERS in the master file that reference those. What this will give you is that the end user can quickly choose the appropriate filter ie 'Beginning of Last Week' and make it easier to apply those where conditions.

Sorry for the late reply had a long weekend.

So your saying these dates can be "set" in a separate file and included on the server end to all tables and referenced in define fields in IA?

I don't have any code access and am limited to IA or I guess IA+ now, but can get a developer to set this up if it's a one time thing that doesn't need to be constantly maintained. I just need to make sure I'm understanding what is possible here.

Hi Cimmderian

Yes it is a one time thing and would be glad to help your developer set it up.

Regards,