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

kemmy1 · ‎07-02-2019

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 · ‎07-02-2019

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:

View solution in original post

Uncle Rob · ‎07-09-2019

Hey Kemmy,

Curious if this solution is still holding up for you? I feel like there's a massive logical gap, but I can't keep my demo data up to date to catch it.

kemmy1 · ‎07-15-2019

I'm still struggling with this as well. I took out ALL the other stuff to focus on trend and relative and it's still not working.

Here are the records that I should be gathering:

But then when I try trend and related queries, I get this. It's only bringing back the ones with a closed date on Sunday. I've even tried after 5 days ago and it's still only capturing 7/14.:

kemmy1 · ‎07-15-2019

This MIGHT be the answer:

kemmy1 · ‎08-09-2019

Nevermind, this didn't work either. I decided just to create a new true/false field in the background and call it "yesterday". If it's Monday, a scheduled job checks it as true, if it's not Monday, a scheduled job checks it as false and then add this condition to my report.

christopherdenn · ‎04-26-2020

Morning:

Not sure if anyone resolved this. This seems to work for me. Took what you provided (that didn't work for me) and then started to tweak/modify until it worked. This is to hopefully say on Monday show me just Saturday and Sundays data. On Tuesday, I have a simple Or clause stating similar but Planned End Date is Yesterday (this is not shown). Only showing what I have displays on Monday for the immediate past Saturday and Sunday. Let me know if it works for you.