Create an Open Orders chart with multiple dates per fact record

[SDT]

I learned a lot (no where near enough) on these forums and I'm hoping someone can help with the latest problem. I have a fact table with multiple dates per fact. The unique fact key is WorkOrder and I have used a bridge table to connect the facts to a fiscal calendar. I also have an as-of calendar for rolling rate of change calculations.

I cannot figure out how to create a line or bar chart showing the $ value of open (sum(OpenOrInvoicedAmt) orders on a given day in the fiscal calendar. It has been driving me quite mad as it seems like this would be simple. I bet it could be done in Excel by listing all the fiscal calendar dates and doing a SUMIFS statement with the OrderDate and ShipDate.

To complicate matters, the ShipDate is null for orders not yet shipped as of the last data load.

Please help me see what I am missing ?!?!

[SDT · Accepted solution]

I have to credit Joe Ho and Rob Mohr from Qlik for this solution.

I created a stand alone canonical calendar (a data island) and used a Cartesian join to find open orders on dates in that calendar. Dimension and Measure are below.

X-Axis dimension only showing working days:

=IF(CCDate<=(Today(0)-1) and MATCH(CCDate,$(vHolidays))=0,CCDate) 

Y-Axis Open orders on each day:

sum({$<CMorOrder={'Work Order'}>} (OrderDate <= CCDate and ShipDate >= CCDate)*-1)
+
sum({$<CMorOrder={'Work Order'}>} (OrderDate <= CCDate and isnull(ShipDate))*-1)

This of course works for any of the other values associated with the order number. Simply multiple by the value at the end of the Cartesian join.

Enjoy!