Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fortehealth
Contributor II
Contributor II

Calculate the number of open orders for each date in calendar

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 IDDate
Order Date CreatedOpen 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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See this blog post: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
agni_gold
Specialist III
Specialist III

Can you please send some sample data and app here.

Gysbert_Wassenaar

See this blog post: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand
fortehealth
Contributor II
Contributor II
Author

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

fortehealth
Contributor II
Contributor II
Author

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.

fortehealth
Contributor II
Contributor II
Author

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
Champion III
Champion III

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
Champion III
Champion III

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.

fortehealth
Contributor II
Contributor II
Author

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
Champion III
Champion III

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