topic Re: Determine Average Number of Days Overdue in QlikView

Determine Average Number of Days Overdue

djbloiss — Wed, 19 Sep 2018 15:55:31 GMT

I need to figure out, out of all overdue orders, what is the average days overdue for street sales and non street sales (separately). Every field is in the same table. This is what I first tried:

if(Today()-avg({$<Open={1}, [Invoice Number]-={''}>} [Invoice Due Date Num])>0, Today()-avg({$<Open={1}, [Invoice Number]-={''}>} [Invoice Due Date Num]),0)

Here is another way I tried it:

Today()-(Only({$<Open={1}, [Invoice Number]-={''}>} [Invoice Due Date Num])*Count(DISTINCT aggr([Invoice Number], [Invoice Due Date Num])))

A few points about this data:

1) The problem is there can be multiple records for the same [Invoice Number]. Each record for [Invoice Number] would have the same due date

2) StreetSalesFlag is a 1 if it is a street sale and null if it is not.

3) I am excluding any records that have a blank for [Invoice Number].

4) Open is 1 if the Invoice is still open. We don't want to include any closed orders

What am I doing wrong?

Re: Determine Average Number of Days Overdue

captain89 — Wed, 19 Sep 2018 16:54:48 GMT

Hi,

I suggest you to solve the problem in data model. (i made a similar analysis).

If you have multiple records for the same inv. number you need to make a rule to do a weighted average.

First of all you should have in the same row the delivery date and the delivery request date by the consumer.

Usually you have :

Invoice Date : sales table,

Delivery Date: sales table,

Delivery request Date: orders table.

you can do a mapping load like this:

map_delivery_req_date:

mapping load orders_order_key, delivery_req_date resident orders;

Sales:

load*,

applymap ('map_delivery_req_date', sales_order_key, delivery_date) as delivery_req_date

resident SalesW;

Drop Table SalesW;

Then you can do the difference between date:

Interval(delivery_date - delivery_request_date) or

if(Interval(delivery_date - delivery_request_date) >0, Interval(delivery_date - delivery_request_date), 0)

Then you should decide the rule (for example value)

sum({ if(Interval(delivery_date - delivery_request_date) >0, Interval(delivery_date - delivery_request_date), 0)*quantity*price)

/sum (total quantity*price)

if you want to analize only non-processed order change the formula:

sum(<InvoiceNo={''}>} if(Interval(today() - delivery_request_date) >0, Interval(delivery_date - delivery_request_date), 0)*quantity*price)

/sum (total {<InvoiceNo={''}>} quantity*price)

Re: Determine Average Number of Days Overdue

djbloiss — Wed, 19 Sep 2018 17:38:23 GMT

why should I do a weighted average? I want an invoice to be distinct. I only want to count it once.

Re: Determine Average Number of Days Overdue

captain89 — Thu, 20 Sep 2018 09:52:29 GMT

In Italy you may have more than one delivery note with different delivery request date in the same invoice.

If you want a complete view of delivery delays as usual.. you should make a rule to do a weighted average.

So is for you a delivery delay of 3 days for 1000$ of goods the same of a 10000$ one?

In my opinion you should treat differently the invoices with a greater amount.

I did the analysis with 2 Kpis:

Service Level (weighted - based on amount) (% of deliveries processed in time over the total)

Avg Delivery days od delay ( weighted - based on amount)

This is a way to have a complete view of the situation.

Can you attach a sample table ? I would help you but i don't understand the logic of your dataset.