The outcome you want I think is also known as "queue length" as you want to show, for a given day, how many orders are in flight but subtract those closed the day before and add those opened that day.
There are a number of solutions proposed in the thread below:
Hope that helps
Thanks for your reply, but I may not have been clear about what my specific concern was. The problem I am having is not about how to perform the "queue length" calculation itself, but rather how to plot that answer across a series of dates. The issue I have is that the [Date of Interest] values desired for plotting need to be independent of [Order Date] and [Ship Date].
I would like [Date of Interest] to be all the days between a user selected start/end period within the range of MIN([Order Date]) and DATE(NOW()).
I would like to have [Date of Interest] on the Columns Shelf, and [Open Count] on the rows, but I can only get [Date of Interest] to be a single date, and not all the dates within a range.
My guess is that this is some sort of 'data densification' problem, but I am not strong on how that approach works. There may be an easier way, but I am stuck.
Sorry Brian, my wording wasn't very helpful!
You already have the calculation for a 'given day', what I should've said is that to plot the result for all days in the dataset richard leeke's reply in that forum question I linked above shows how to do that through custom SQL and a running sum on the [Delta] value from his SQL.
Unfortunately, you don't get the option to use custom SQL on excel datasources in 9.3 so it'd require reshaping the data in excel to create two tables (one for start dates where start date = order date and one for end date where end date = shipped date) and union them together using the Union feature in the data connection screen.
I can mock this up tonight if you'd like a working example.
Found some time to provide an example of what i meant, hope it's what you had in mind!
The attached twbx has a union datasource which I created by copying the first few columns of the Orders worksheet from sample - superstore into two separate sheets OrdersStart and OrdersEnd (could've combined in excel but wanted to show the union feature).
I then added an EventDate column and EventType and for respective sheets set these fields to order date and order / ship date and ship.
Using the union feature I appended those together and created a "Delta" field that'll be used to say how many orders are open (ordered not shipped) for each day.
This calculated field is:
IF [EventType] = 'Order' THEN 1
ELSEIF [EventType] = 'Ship' THEN -1
Which essentially says, if there's an order, add one, if an order has been shipped, subtract one.
Showing the running sum of this field (as shown in the workbook) across EventDate will show the volume of open orders each day.
please get in touch if this doesn't help.
OpenOrders.twbx 380.2 KB