Case Fill rate On future Date

[mohitd910]

Dear Friends,

Sample Test Excel is attached only for one date when i am selecting 12- Feb-2017.

I have data like this:-

OrderDate	Order	customer
1-Feb-17	20	A
2-Feb-17	25	b

Delivery Date	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

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
1-Feb-17	20	19	95
2-Feb-17	25	21	84

Regards,

Mohit Dahiya

Message was edited by: Mohit Dahiya

[Kushal_Chawda]

Create a link on both the tables on Customer.

Create a straight table

Dimension:

Order Date,Customer

Expression:

Sum(Delivery Qty)/Sum(Order)*100

[mohitd910]

I dont want order date as a dimension.

[vinieme12]

Your scenario seems exactly similar to what HIC has described here

Canonical Date

[MK_QSL]

You can use this..

SET DateFormat='D-MMM-YY';

Order:

Load * Inline

[

  OrderDate, Order, Customer

  1-Feb-17, 20, A

  2-Feb-17, 25, B

];

Delivery:

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

];

Calendar:

Load Date(MinDate + IterNo() - 1) as DeliveryDate While MinDate + IterNo() - 1 <= MaxDate;

Load

  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

Dimension

OrderDate

Customer

Expression

SUM([Delivery qty])/SUM(Order) * 100

[mohitd910]

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.

Canonical

[Kushal_Chawda]

Then you can use Delivery date also in dimension. Just link your delivery table with Order on Customer dimension

[vinieme12]

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?