11 Replies Latest reply: Sep 16, 2016 2:47 PM by Robson Candeo

# Problems with Rank when has equal values or null

!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

• ###### Re: Problems with Rank when has equal values or null

Which one is the right one??

• ###### Re: Problems with Rank when has equal values or null

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?

• ###### Re: Problems with Rank when has equal values or null

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])

• ###### Re: Problems with Rank when has equal values or null

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

• ###### Re: Problems with Rank when has equal values or null

Robson -

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

Best,

Sunny

• ###### Re: Problems with Rank when has equal values or null

thank you so much. Have a good night

• ###### Re: Problems with Rank when has equal values or null

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

• ###### Re: Problems with Rank when has equal values or null

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

• ###### Re: Problems with Rank when has equal values or null

Thank you

• ###### Re: Problems with Rank when has equal values or null

Try this:

Pick(Dim,

Round(Rank(If(Sum({1<[Reporting Calendar Year]={'\$(vSelectedYear)'},[Reporting Calendar Quarter]={'\$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} \$(vCurrency)) <> 0,

Sum({1<[Reporting Calendar Year]={'\$(vSelectedYear)'},[Reporting Calendar Quarter]={'\$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} \$(vCurrency))),1,1) * Avg(1)),

Round(Rank(Sum({1<[Reporting Calendar Year]={'\$(vYearBefore)'},[Reporting Calendar Quarter]={'\$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} \$(vCurrency))) * Avg(1)

-

Rank(Sum({1<[Reporting Calendar Year]={'\$(vSelectedYear)'},[Reporting Calendar Quarter]={'\$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} \$(vCurrency))) * Avg(1)),

Num(Sum({1<[Reporting Calendar Year]={'\$(vSelectedYear)'},[Reporting Calendar Quarter]={'\$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} \$(vCurrency)) * Avg(1)

/

Sum({1<[Reporting Calendar Year]={'\$(vYearBefore)'},[Reporting Calendar Quarter]={'\$(vSelectedQuarter)'},[High Level Risk Code Description]={'Aviation'}>} \$(vCurrency)) * Avg(1)

-1,'+ #,##0.00%;(#,##0.00)%'))

Did this for Aviation only, you will probably need to do this for all expressions

• ###### Re: Problems with Rank when has equal values or null

thank you again so much