Reporting question: include Friday, Saturday and Sunday for Create Date if Today's date is Monday

[kemmy1]

Our client first requested a pretty common report of all SR's (under a certain item) that was created "yesterday." So I created a simple report and thought that was that.

But what they really want is a report of all SR's that were created "yesterday" but if it's Monday, have "yesterday" include not only Sunday, but Saturday and Friday as well. 

I'm thinking of creating a new field (text) and run a scheduled job to script out what is "yesterday", run it daily and put "yesterday" into this new field and report on that. (and of course blank out the already existing records with "yesterday" if it doesn't fit the bill anymore).

Is this the best solution?

Thanks!

Lisa

[Uncle Rob · Accepted solution]

Don't create new fields.  If you use trend and relative operators correctly, you should be able to express this.  You just need to make it a whole other OR condition to the "created yesterday" line.  Don't click the OR at the end of the condition line.  Click the NEW CRITERIIA button at the bottom of the report builder!!

Anyway... this will make a whole new query condition that selects anything from three days ago so long as its also on a Friday, Saturday, Sunday, or Monday.

Knowing the Trend operator is critical for this kind of complexity.  I made a video about it here:

[Uncle Rob]

Actually... darn it.  This won't work eaither.  Because on Tuesday you'll Monday Sunday Saturday.  On Wednesday you'll get Sunday & Saturday, and on Thrusday you'll get Saturday.

Needs some more thought.

[Uncle Rob]

ARRG.  I can't figure out a way to express this with just condition builder.  I keep running into the problem of looking back on a four day interval should only happen on Mondays... but that's saying "Today is Monday".  Any expression that I use to "reach back X days" will also ruin results on Tuesday.  😞

[kemmy1]

I'm not sure what you are experiencing, but so far, it's working for Tuesday and Wednesday for me (it's ONLY grabbing yesterday's info like I want it to).  But I need to test it on Monday to see if it includes Friday, Sat and Sun.  I'll keep you posted!