
Re: Delivery on Time  Noobie formula question
jan.luchterhand Jun 23, 2011 9:05 AM (in response to jan.luchterhand)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???

Re: Delivery on Time  Noobie formula question
Johannes Sunden Jun 23, 2011 10:13 PM (in response to jan.luchterhand)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])

SumSetAnalysis.qvw 133.8 K

Re: Delivery on Time  Noobie formula question
jan.luchterhand Jun 24, 2011 2:55 AM (in response to Johannes Sunden )Thank you Johannes!
That's exactly what I needed!

