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.
|Order Date||Shipping Date (Invoice Date)||Order Number||Amount (thousands of USD)|
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.
|Date||Year||Month||Open Order Amount|
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.
Take a look on this functions that can help you to find out the first day of the month and then calculate the intervals.
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
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
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?
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 !