topic Re: Problems with Rank when has equal values or null in QlikView

Problems with Rank when has equal values or null

rcandeo — Mon, 12 Sep 2016 18:38:15 GMT

!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

sunny_talwar — Mon, 12 Sep 2016 19:38:26 GMT

Which one is the right one??

Re: Problems with Rank when has equal values or null

rcandeo — Mon, 12 Sep 2016 20:01:12 GMT

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

sunny_talwar — Mon, 12 Sep 2016 20:13:22 GMT

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

Re: Problems with Rank when has equal values or null

rcandeo — Tue, 13 Sep 2016 00:38:15 GMT

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

sunny_talwar — Tue, 13 Sep 2016 00:56:21 GMT

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

rcandeo — Tue, 13 Sep 2016 01:07:40 GMT

thank you so much. Have a good night

Re: Problems with Rank when has equal values or null

rcandeo — Tue, 13 Sep 2016 12:21:59 GMT

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

sunny_talwar — Tue, 13 Sep 2016 20:42:34 GMT

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

rcandeo — Wed, 14 Sep 2016 03:05:07 GMT

Thank you

Re: Problems with Rank when has equal values or null

sunny_talwar — Wed, 14 Sep 2016 06:02:50 GMT

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

rcandeo — Fri, 16 Sep 2016 18:47:46 GMT

thank you again so much