Big Problem I do not know how to describe

[YannikPrill]

Hey everyone,

it seems like I have a bigger Problem where I'm not even sure if there is a solution. Let me try to explain. Here we go:

I have a big table with lots of suppliers listed in. Each supplier can make multiple mistakes in the project I'm observing. The data pool lists every mistake the suppliers make and linkes the mistake to the supplier number. There are lets say 10 sifferent mistakes that can occure. What I try to create is a table that lists every supplier and his top 3 mistakes - ergo the mistakes that occure the most. It should look somewhat like this:

first column: supplier number | second column: top mistake | third column: number of top mistakes | fourth column: second most mistake | fifth column: number of second most mistake

and so on...

Now in my case the top 3 mistakes the suppliers can make can differ from each other. Some may have mistake 1, 4 and 7 where another supplier peaks at mistakes 3, 9 and 10.

The table should then list each supplier with his top 3 mistakes and the number of these mistakes.

To give an example lets call the data pool of supplier numbers "X" and the mistakes "1", "2", "3", ....

I hope you can understand my problem and may help with finding a solution 🙂

Greets,

Yannik

[YannikPrill]

Aggr(
IF(
rank(sum([Inc Service Name]),4)=2,
count([Incident-Nr])
),
[Inc Lieferant Name], [Inc Service Name])

I already did replace it 😄

[Yoshidaqlik]

or if you put in a total aggregation as suggested

but should not change the result

sum(
aggr(
	IF(
    	  rank(sum(Value),4)=1,
          sum(Value)
        ),
supplier,mistake)
)

[YannikPrill]

No that just gave me a total value on top of the column.

I dont think the mistake is in your formula as it seems to work. I need to do some internal research and speak to my coworkers... maybe they have an idea on how to solve this.

Thanks alot for your help 🙂

[Yoshidaqlik]

the rank formula must be the same as the IF result

Aggr(
	IF(
		rank(count([Incident-Nr]),4)=2,
		count([Incident-Nr])
	)
,[Inc Lieferant Name], [Inc Service Name])

[YannikPrill]

I tried it like that the first way... I got the same results both ways, so I thought it would make no difference 🙂

Thx again for your time and help... I'll mark your answer as a solution and will get back to you as soon as I found my mistake.

Greets