Delivery on Time - Noobie formula question

[#1]

Dear Community & Experts,

as I am still a noobie with QlikView I hope you can help me to solve the following task.

I habe a list of our suppliers and I would like to know, how their Delivery on Time accuracy is. I have attached a short excel file, which you

can use.

The way we want to calculate the DOT is like this:

We take all lines of one (the same) supplier in which the order status (column L) is 5. In column S we see, if this supplier has delivered in time.

If there is a 1 in column S, then he was on time. If there is a 0, he was not.

In my example list the company "Schulzenschnulze AG" has 21 lines with order status 5. 20 of these lines have a 1 in column S, 1 line has a 0.

That means 20 out of 21 deliveries were on time, so the DOT is  95,2%.

What are the right formulas to:

- count the number of lines of each supplier, where the order status in column L is 5

- count the 1's in column S for these lines

- divide number of 1's of column s by the number of lines for supplier X with status 5 in column L

I hope you get what I mean.

The data source will be an excel file like the one I've attached.

Thanks in advance for your help!

[#2]

Maybe I have to simplify my question... in Excel the formula I am looking for is most likely "countif"...

Let's say I have the following table:

Supplier   Order Status   DOT

A                5                    1

A                5                    1

A                5                    0

A                5                    1  

B                5                    1

B                3                    

B                5                    1

a) I want to count the number of lines of Supplier A, only if the status in the second column is 5.

Should be something like: if(Order Status="5", count(Supplier), "N/A") but that doesn't work

The result should be: A count 4; B count 2

b) I want to count the number of 1's in column DOT for the above calculated number of rows for each supplier with order status 5. No idea about the formula, the result should be for Supplier A: 3, for B: 2.

c) Now I want to divide result b) by a)... so for

Supplier A: 3 times 1 / 4 rows with status 5: DOT accuracy 75% (3 out of 4 deliveries on time)

Supplier B: 2 times 1 / 2 rows with status 5: DOT accuracy 100% (2 out of 2)

Any ideas about the formula???

[Anonymous · Accepted solution]

Hi Jan,

Here's an example using Set Analysis. Please see attached file.

Expressions are:

1. For counting the number of 5 occurences per supplier:

Count({<[Order Status]={5}>} [Order Status])

2. For counting the number of 1 occurences in DOT per supplier:

Sum({<[DOT]={1}>} [DOT])

3. For creating the percentage of deliveries on time:

Sum({<[DOT]={1}>} [DOT])

/

Count({<[Order Status]={5}>} [Order Status])

[#4]

Thank you Johannes!

That's exactly what I needed!