Our Standard week in WebFocus is Saturday - Friday.

I need to change this for a specific Info Assist report to Sunday - Saturday.


So I have my Week Ending Define based on my date field:
FIELD_WEEK_END_DATE/MDYY=DATEMOVE(FIELD_WEEK_END_DATE, 'EOW')

MY QUERY IS AS FOLLOWS

SUM HOUR_COUNT
BY GROUP
ACROSS FIELD_WEEK_END_DATE
WHERE FIELD_WEEK_END_DATE GE '01/16/2015' AND FIELD_WEEK_END_DATE LE '01/29/2015';

SO IF I QUERY THE ABOVE THE DATE RANGE
01/16/2015 FALLS INTO THE WEEK ENDING OF 01/16/2015
01/17/2015 - 01/23/2014 FALLS INTO THE WEEK ENDING OF 01/23/2015
AND
01/24/2014 - 01/29/2015 FALLS INTO THE WEEK ENDING OF 01/30/2015

NEED THIS WEEKENDING DATE TO BE:
01/16/2015 FALLS INTO THE WEEK ENDING OF 01/17/2015
01/17/2015 - 01/23/2014 FALLS INTO THE WEEK ENDING OF 01/24/2015
AND
01/24/2014 - 01/29/2015 FALLS INTO THE WEEK ENDING OF 01/31/2015

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

Add SET WEEKFIRST = 1 to the program

This will affect all reports:
Reporting Server: Workspace --> Configuration Files --> Server Profile - edasprof.prf:

SET WEEKFIRST = 1

hth

Thank you for the reply, we do not want to affect all reports so a SET command globally or in the RO is not an option. If added directly to the .fex file then we loose our ability to open the report in the IA editor.

We need a way to do this using the define tool, maybe? I am drawing a blank on the how to....

Well, then you have use this method:

Add SET commands in InfoAssist

Lynsi,

i haven't tested this yet, but how about the following:

FIELD_WEEK_END_DATE/MDYY=
DATEADD(DATEMOVE(FIELD_WEEK_END_DATE, 'EOW'),'D',1)

Thanks
Luiz

Lynsi,

Let us know if any of the previous posts worked for you.

Thank you for sharing with Focal Point and if your questions have been resolved can you mark the subject line for the post as [SOLVED].

Kindest regards,
Tamra Colangelo
InfoAssist Focal Point Forum Moderator

Thank you Tamra for following up, although the suggestions were great, unfortunately I was not able to resolve our issue with these suggestions in Info Assist. The Power User I posted for has come up with a work around so i am no longer seeking suggestions.

Thank you!

Lynsi,

You might try to set up a DEFINE in the master for that specific date format as the other member has suggested and have your user be aware of using the DEFINE field in the WHERE. Just be aware that WHERE based on defined fields can cause some response delays depending on the number of records retrieved.

The subject will be marked [CLOSED] within 2 days unless you have further questions.

Kindest regards,
Tamra Colangelo
InfoAssist Focal Point Forum Moderator
Information Builders Inc.