Calculate the number of open orders for each date in calendar

[Anonymous]

Hi all,

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]

Orders	Calendar
Order ID	Date
Order Date Created	Open Orders
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

School - number of students enrolled

Would appreciate any pointers

Cheers

James

[agni_gold]

Can you please send some sample data and app here.

[Gysbert_Wassenaar · Accepted solution]

See this blog post: Creating Reference Dates for Intervals

[Anonymous]

Thanks very much for the replies

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

[Order ID]      [Date Created]        [Date Closed]

1                    2016-03-10              2016-03-14

2                    2016-03-13              2016-03-16

3                    2016-03-17                     -

Calendar

[Date]            [Open Orders]

2016-03-10     1

2016-03-11     1

2016-03-12     1

2016-03-13     2

2016-03-14     1

2016-03-15     1

2016-03-16     0

2016-03-17     1

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

Cheers

James

[Anonymous]

Actually having said that, it does seem like a nice solution as it means you can filter. But I must need to tweak it as it seems to be showing a higher number than should actually be.

I'll still need to figure out the set analysis though so I can exclude any current filters for an overview screen though.

[Anonymous]

I figured it out using the Creating Reference Dates for intervals

[Inpatient Statistics]:

Load

  [Admission ID]&'_'&IterNo() AS [%Inpatient Statistics],

  1 As [Inpatients],

  Date([Admission Date]+IterNo()-1) As [Inpatient Date]

  Resident Procedure

  // Add rows until we reach the discharge date, and count any patients not yet discharged

  While [Admission Date]+IterNo() <= If(IsNull([Discharge Date]),Today()+1,[Discharge Date]);

[johnw]

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.

See attached.

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.

[johnw]

You could get a count for a date with set analysis.

=count({<Created*={"=Created<=makedate(2016,1,12)"},Closed*={"=Closed>makedate(2016,1,12)"}>} distinct Order)

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.

[Anonymous]

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

[johnw]

Glad you were able to handle it with the row per date per 'order'. That seems cleanest.