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

[Yoshidaqlik · Accepted solution]

Hi

I will insert as I would in a table,
maybe you can help adapt the expression as you need it

1 - column

supplier

2 - column = Measure

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

3 - column = Measure

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

4 - column = Measure

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

5 - column = Measure

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

regards

[YannikPrill]

Thx for the quick reply 🙂

So for "supplier" and "mistake" I would need to set the name of the datafields right?

I will try it and reply asap 🙂

Greets

[marcus_sommer]

Another approach might be this one:

concat(aggr(if(rank(count(mistakes))<=3,mistakes), supplier), ' + ', count(mistakes))

whereby I think the syntax from the (bold) third parameter needs adjustment (and without special reeasons I would tend  to use just two columns - one for the mistakes and one for their numbers - and not multiple ones).

Is the above calculation not really dependend from selections it might be sensible to calculate this within the script and not in the UI.

- Marcus

[YannikPrill]

Hey Yoshidaqlik,

so your solution somehow seems to work. I at least get different mistakes and numbers to each mistake. But for some reason the ranks seem not to fit:

any idea on this?

Thx alot 🙂 

Greets,

Yannik

[YannikPrill]

Hey Marcus,

from your name I asume you speak German? 😄

So far your solution did not work for me. As I said I am very new to this. Maybe I got your formula wrong.

Yoshibaqliks solution kind of worked for me though. Maybe you can help adjusting my problem with his solution. See the post above 🙂

Greets,

Yannik

[marcus_sommer]

Your guess with German is right 😁

I'm not surprised that's not worked at once. Like mentioned I assume that the third parameter isn't applied correctly (I'm still on QV 11 and have therefore no practice with this new parameter but I know that you could apply an expression for it). Just for testing the main-logic behind it you could remove this parameter.

Further you might need more/other dimensions within the aggr if there are more dimensions and/or selections involved.

The approach from Yoshidaqlik might not work because it's not are real expression else a calculated dimension because it missed the outer aggregation like: concat/sum/count.

- Marcus

[Yoshidaqlik]

I didn't understand well   😛
what does not fit?

[YannikPrill]

Well, it seems like my table has some sort of ranking in within it. But as you can see the values do not represent the ranks. The values should descend from left to right for each row since the mistake with the most errors should be displayed as rank 1 and so on.

In the screenshot you can see that in some rows rank 3 has a higher number of mistakes than rank 2 has.

I hope I could describe it for your understanding 🙂

[Yoshidaqlik]

maybe it's the case of replacing sum with count
paste the formula you used in the first measurement