Sample Test Excel is attached only for one date when i am selecting 12- Feb-2017.
I have data like this:-
|Delivery Date||Delivery qty||Customer|
Now i want to find out case fill rate on the basis of my calendar date
Like 1 -Feb what will be my case fill rate . For customer A on Date 1 Feb there are 20 orders. it will deliver in future dates like 2 Feb , 3rd feb so on.
Case fill rate for customer A for 1 Feb total delivery for that order till now / total order *100.
Like:- 19/20 *100 for customer A for 1 feb order.
In my Data Model Delivery date is connected with Master calendar.
My delivery date is connected with master calender.
i don't want to create a copy of order table to connect with master calendar in date model because of performance issues.
Output will Look Like.
|Date||order||Delivery||Case fill rate|
Message was edited by: Mohit Dahiya
You can use this..
Load * Inline
OrderDate, Order, Customer
1-Feb-17, 20, A
2-Feb-17, 25, B
Load * Inline
DeliveryDate, Delivery qty, Customer
2-Feb-17, 15, A
3-Feb-17, 3, A
4-Feb-17, 1, A
3-Feb-17, 17, B
4-Feb-17, 4, B
Load Date(MinDate + IterNo() - 1) as DeliveryDate While MinDate + IterNo() - 1 <= MaxDate;
RangeMin(Min(FieldValue('OrderDate',RecNo())),Min(FieldValue('DeliveryDate',RecNo()))) as MinDate,
RangeMax(Max(FieldValue('OrderDate',RecNo())),Max(FieldValue('DeliveryDate',RecNo()))) as MaxDate
AutoGenerate FieldValueCount('OrderDate') + FieldValueCount('DeliveryDate');
Create a Straight Table
SUM([Delivery qty])/SUM(Order) * 100
bro i dont want to change in Date model performance issue will rise. I know i can do it through data model just creating the copy of order table and connect it with master calender. i know in this we will create a date field with same name field.
can you also upload sample data for your Order table, the excel only has Delivery table data, also is there an order ID that links both these tableS?