4 Replies Latest reply: Nov 30, 2017 2:48 PM by omar bensalem RSS

    Open orders at End on Month

    Sandra Pinto

      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.


        • Re: Open orders at End on Month
          Ricardo gerhard

          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

          • Re: Open orders at End on Month
            omar bensalem

            It would be sthing like this :


            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



              • Re: Open orders at End on Month
                Sandra Pinto

                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

                  • Re: Open orders at End on Month
                    omar bensalem

                    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 !