topic Re: Case Fill rate On future Date in QlikView

Case Fill rate On future Date

mohitd910 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Case Fill rate On future Date

Kushal_Chawda — Fri, 17 Feb 2017 10:14:33 GMT

Create a link on both the tables on Customer.

Create a straight table

Dimension:

Order Date,Customer

Expression:

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

Re: Case Fill rate On future Date

mohitd910 — Fri, 17 Feb 2017 10:45:46 GMT

I dont want order date as a dimension.

Re: Case Fill rate On future Date

vinieme12 — Fri, 17 Feb 2017 11:19:14 GMT

Your scenario seems exactly similar to what HIC has described here

Canonical Date

Re: Case Fill rate On future Date

MK_QSL — Fri, 17 Feb 2017 11:21:41 GMT

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

Re: Case Fill rate On future Date

mohitd910 — Fri, 17 Feb 2017 11:28:06 GMT

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

Re: Case Fill rate On future Date

Kushal_Chawda — Fri, 17 Feb 2017 11:51:20 GMT

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

Re: Case Fill rate On future Date

vinieme12 — Fri, 17 Feb 2017 11:54:18 GMT

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?