Need to graph the number of open items in a DB based on the # of days it has been open

Question

I am working on a WO Aging report and I need to figure out the following information:

1. Need to group by Trade
2. Need to group by Status
3. Need to know how many work orders are in each status that are:
   • open for 1 week
   • open for 2 weeks
   • open for 3 weeks
   • open for 4 weeks
   • open 5 weeks or more

Is there a way to write an if...else..then statement that will allow me to count the number of records within each week?

Answer 1

Sure. If you want the weeks as columns, it's just something like

(case when <your date range comparison> then work_order else null end)

You'd add a new calculation like that for each bucket, and set the aggregation to be count. The trickiest part is probably going to be deriving your week buckets.