0

I am trying to create a search that shows the number of cases that are open today (would be run on a Monday morning), the number that were open last Monday morning, and the number that were open the Monday morning before that.

Problem is, I can not find any way of telling the search to basically pretend it was last week for these results, and the week before for those results.

I can't use any fields that exist on the forms, as they would never point to the date that we are viewing the search.

The logic seems easy:

count(cases open now)
count(cases open last Monday @ 08:00)
count(cases open two weeks ago Monday @ 08:00)

The NetSuite solution is just evading me. Anyone else ever run into this? Seems like it would be pretty common.

w3bguy
  • 2,215
  • 1
  • 19
  • 34

1 Answers1

2

Can you use the fact that anything that was open last Monday is anything that was created before last Monday and not closed before last Monday. Open Last Monday = date created < last monday AND (date closed is empty or date closed > last monday)

Paul Gregson
  • 156
  • 3
  • 15
  • That's a good thought. I'll try that tomorrow and see if that could work. You definitely might be on to something. Thanks. – w3bguy Aug 30 '15 at 16:20
  • Marking this as correct. It did work for me. I just set a CASE WHEN statement to look at {today}-7, and am checking the open and closed dates against that. Simple, easy solution. Thanks! – w3bguy Aug 31 '15 at 14:04