Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sandrapinto
New Contributor II

Open orders at End on Month

Hi everyone.

I would much appreciate your help with the following:

I have a list of customer orders from which I need to get the Open Order Amount  at the End of each Month.

An Open Order is one that has been entered into the system and has not yet been supplied to the customer.

So, for example, order number 222 in TABLE 1 below is considered open from Jan 1 until March 14, 2017,as this is one day before the shipping date. On the Shipping date itself, this order is considered to be closed as this is when we supply the product and issue an invoice for it.

TABLE 1:

Order DateShipping Date (Invoice Date)Order NumberAmount (thousands of USD)
01/01/201715/01/2017111          100
01/01/201715/03/2017222          100
01/01/201730/06/2017333          100
01/02/201715/05/2017444          200
15/02/201731/07/2017555          300
20/04/201730/11/2017666          300
10/07/2017-777          500
01/11/2017-888        1,000


Below in TABLE 2 is  how I would like to present the open order amounts in the UI (I would like to be able to present it as table and as a bar chart also).

Let me try to explain the Logic:

An open order amount is the amount of orders that have entered the system before or at the end of the selected month and were not  shipped before or at the end of that selected month.


So. for example:

On Jan 31, 2017, I have an open amount of $200K: Orders no. 222 and 333 which were entered on before, or on, Jan 31 and have not been shipped prior to, or on, Jan 31. Order 111 is not open since it was shipped on Jan 15 and is no longer open on Jan 31.

On Feb 28, 2017, I have $ 700K: Orders 222, 333, 444, 555 which were all entered before or on Feb 28 but were not shipped before or on Feb 28.

On March 31, 2017, I have $ 600K: Orders 333, 444, 555 which were entered before or on March 31 and were not shipped before or on March 31. Order 222 is no longer open at this point since it was shipped on March 15.

Let's jump to Nov now..

At the end of Nov there is an open amount of $1.5 M: Orders 777 and 888 which were entered before or on Nov 30 and were not shipped before or on Nov 30 (no ship date for these orders since they were not shipped yet as of this time). Order 666 was shipped on Nov 30 so that at the end of the day of Nov 30, it is no longer open.


TABLE 2:   

DateYearMonthOpen Order Amount
31/01/201720171           200
28/02/201720172           700
31/03/201720173           600
30/04/201720174           900
31/05/201720175           700
30/06/201720176           600
31/07/201720177           800
31/08/201720178           800
30/09/201720179           800
31/10/2017201710           800
30/11/2017201711        1,500

My question is:

How do I create a table in which I can see the Open Order Amount  for the End of each month , as shown in the TABLE 2  above?  How should I create the measure? What formula should I use to create the Date / Month / year Dimensions? Should I use interval match in the script? if so, how?

Not sure at all how to go about creating this table in the UI.


I really hope  I have managed to explain myself and my question.

Thanks in advance for any help.


Sandra


4 Replies
dcj
Valued Contributor II

Re: Open orders at End on Month

Dear Sandra,

   Take a look on this functions that can help you to find out the first day of the month and then calculate the intervals.

monthsstart - script and chart function ‒ Qlik Sense

monthstart - script and chart function ‒ Qlik Sense

omarbensalem
Esteemed Contributor

Re: Open orders at End on Month

It would be sthing like this :

table1:

load * where MonthEnd < "Shipping Date";

load date(Date#("Order Date",'DD/MM/YYYY')) as "Order Date",

date(Date#("Shipping Date",'DD/MM/YYYY')) as "Shipping Date",

"Order Number", Amount,

Month(Date#("Order Date",'DD/MM/YYYY')) as "Month",

Year(Date#("Order Date",'DD/MM/YYYY')) as "Year",

MonthEnd(Date#("Order Date",'DD/MM/YYYY')) as MonthEnd

Inline [

Order Date, Shipping Date, Order Number, Amount

01/01/2017, 15/01/2017, 111    ,      100

01/01/2017, 15/03/2017, 222    ,      100

01/01/2017, 30/06/2017, 333  ,        100

01/02/2017, 15/05/2017, 444 ,        200

15/02/2017, 31/07/2017, 555 ,          300

20/04/2017, 30/11/2017, 666,          300

10/07/2017, , 777,          500

01/11/2017, , 888,        1000

];

Capture.PNG

sandrapinto
New Contributor II

Re: Open orders at End on Month

Hi Omar,

Thanks for your reply.

However, as you can see, the table you have created shows different amounts compared to the table of OPEN ORDER AMOUNT for the end of each month -

For example, Open amount on Jan 31 should be 200K, your table shows 100K.

Am I missing something?

Thanks again

omarbensalem
Esteemed Contributor

Re: Open orders at End on Month

No it's not; this is the table from the script; It shows seperate line for each value; if you drop the the order and shipping dates fields; it will be grouped by the new MonthEnd field.

If you use a table chart; as dimension MonthEnd and as measure: sum(Amount) ; you'll have 200k for jan2017..

You should have tried it !