Calculate the number of open orders for each date in calendar
Wondering if anyone can assist me, trying to figure out how to add to my Calendar table a field which I can use to show the number of open orders over time. I'm not sure why I can't figure it out but perhaps I'm overthinking it as it seems like it should be simple.
I'm sure there must be lots of threads about it here but I haven't had any luck.
Logically what I'm trying to achieve:
Count Distinct Order ID Where [Order Date Created] <= [Date] AND [Order Date Closed] > [Date] As [Open Orders]
Order Date Created
Order Date Closed
Of course the same prinicpal could be applied to...
Hotels - number of guests at point in time
Hospital - number of patients admitted at point in time
That blog post is an interesting solution but I wonder how efficient that way of doing it would be (storing a row for every order and date combination) when I just need an order count for each day. It would probably work quite well, still interested to know if it is possible with set analysis or otherwise though
Sample data would be
[Order ID] [Date Created] [Date Closed]
1 2016-03-10 2016-03-14
2 2016-03-13 2016-03-16
3 2016-03-17 -
[Date] [Open Orders]
and so on
I feel like there must be a set analysis or something which can just look for orders between those dates for each day as it is creating the master calendar
So you'd want to be able to see, say, a trend line of open orders by day?
Well, if we don't want one record per date, attached is the next idea that occurred to me after that. Make an order activity table that has a +1 for the date the order was created, -1 for the date it was closed. Then build an AsOf table that connects all dates to all past dates. Then just sum up the value from the activity table by AsOfDate.
Edit: And I apologize. I didn't notice it was for Qlik Sense. I don't know if the same solution can apply, or if you can open the attached. Here's the three tables in my data model.
But you "can't" make set analysis sensitive to the dimensions of a chart. (You can work around it, but it's horribly complicated, and definitely not what you want here.)
You could make an island calendar disconnected from your data, then do a count(if()) comparing the dates similarly to the above but with a standard if(), which IS sensitive to your dimensions, but it would be slow to calculate on large data sets.
I think you're going to be best off with a data model solution of some sort.
Hi John - thanks for your time replying with assistance - I did actually figure it out using effectively a row per date per 'order', seems quite a nice solution as I can click on a date and see who was there on that date now.
I'm going to check out your AsOfDate solution though for a similar problem because that would probably be quite useful for cases where I just needed a number like a trend line etc