Problems with Rank when has equal values or null

[rcandeo]

!I have an advanced expression to calculate Rank that brings aleatory values when results are equal or zero or null.

The problem is that dependind on the way that the result is visualized by the user, the result is different.

Look at my example attached. It has two differente ways to calculate, one with texts and beside one with table. Depending of the variable, the result will be different in the both ways.

Just open the application, select for example: Irqbsa DZB and you see that some calculations are differente, like Treaty

[sunny_talwar]

Which one is the right one??

[rcandeo]

Treaty for example, is an expression that result is null, as there is no data that correspond to this.

If you look at Sheet4 I created, you will see that there is values for Treaty for only 6 Brokers and has 0 for a 7th one. So the broker that I gave as example, is one that doesn´t have values for Treaty, so it should be 7 or 8 for this and the others that don´t have, do you agree with me?

[sunny_talwar · Accepted solution]

Robson, can you check if adding this helps (will be needed for all expression, but I am doing it form Treaty for proof of concept)

=Aggr(Rank(Sum({$<[Broker Group Name], [Method of Placement Description]={'Treaty'}>} $(vCurrency)), 1, 1)* Avg(1), [Broker Group Name])

Read about the two new arguments I added here -> Rank ‒ QlikView

[rcandeo]

Thank You again Sunny. You are always saving me.

Just one point. It has problems sometimes if has negative values.

Select Ohdgycu Awca & Jsax Qei and look the values for Aviation in the two tables.

One shows 8 and the other 14

[sunny_talwar]

Robson -

I am not sure what might be causing this, I will take a look at this tomorrow.

Best,

Sunny

[rcandeo]

thank you so much. Have a good night

[rcandeo]

One information that I think is important. This problem happens when some brokers has negative values.Most positive, some without values and some negatives

[sunny_talwar]

I know the reason for why this is happening, I am still working on getting a fix for this

[rcandeo]

Thank you