I have a table OPS_WR with a unique key WR_ID and a DATE_REQUESTED as a start date and DATE_COMPLETED as an end date.
I need to calculate all open work orders on a day, but for simplicity sake when we visualize it by month, we would like "May" to show just the open on the first of the month, and "June" to show just the open for the first of June.
I created the expression: Count(if(DATE_REQUESTED<=MonthStart(CanonicalDate) and DATE_COMPLETED>=MonthStart(CanonicalDate),WR_ID))
I created a Canonical Date which all filters are using. Additionally, all Date fields have their own Master Calendar and everything is configured to our fiscal year (July 1-June 30).
My question is, will the above expression give me what the total open work orders are on the first of each month in my visualization (so an open work order started on 8/1 and ending on 9/3 will still show as an open work order if I filter to September)?
If it is correct or I need to make changes, how will I include in the expression to also show count of WR_ID where there is a DATE_REQUESTED, but the order is still open so DATE_COMPLETED is null? I would need the expression to retrieve counts of both.
Thanks!
