Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohitd910
Creator II
Creator II

Case Fill rate On future Date

Dear Friends,

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

I have data like this:-

OrderDateOrdercustomer
1-Feb-1720A
2-Feb-1725b

Delivery DateDelivery qtyCustomer
2-Feb-1715A
3-Feb-173A
4-Feb-171A
3-Feb-1717B
4-Feb-174B

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.

DateorderDeliveryCase fill rate
1-Feb-17201995
2-Feb-17252184

Regards,

Mohit Dahiya

Message was edited by: Mohit Dahiya

7 Replies
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
Creator II
Creator II
Author

I dont want order date as a dimension.

vinieme12
Champion III
Champion III

Your scenario seems exactly similar to what HIC has described here

Canonical Date

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
MK_QSL
MVP
MVP

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
Creator II
Creator II
Author

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
Champion III
Champion III

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?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.