topic Re: Highlighting first highest 3 under each Branch in QlikView

Highlighting first highest 3 under each Branch

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Dear Friends!

If I'am to show first highest three MEs under each depts, how the equation be revised to accommodate said changes.Appreciate if I am provided with a solution!

Regards

Neville

Re: Highlighting first highest 3 under each Branch

sunny_talwar — Tue, 30 Jun 2020 18:59:58 GMT

May be this

Sum(Aggr( IF(Sum(REFUND%) >= Max(TOTAL<BRANCH> Aggr(Sum(REFUND%), ME, BRANCH, REGION), 3), Sum(REFUND%)) , ME, BRANCH, REGION))

Re: Highlighting first highest 3 under each Branch

nevilledhamsiri — Fri, 03 Jul 2020 16:25:28 GMT

Hi Sunny,

Once your formula applied three employees are chosen but not in a descending order such as 1st one being the highest refund ratio holder & second being the 2nd highest etc. Please refer the green coloured column where expected order is not maintained. Should the expression to be amended ?

Thanks

Re: Highlighting first highest 3 under each Branch

sunny_talwar — Mon, 06 Jul 2020 11:42:28 GMT

I am not sure I follow your question. Can you explain what is the issue?

Re: Highlighting first highest 3 under each Branch

nevilledhamsiri — Tue, 07 Jul 2020 16:44:20 GMT

I need to arrange the highest three people under each Branch such as below. The formula doesn't fix this order, isn't it?

BRANCH	ME	REGION	SUM(REFUND%)
EHELIYAGODA	DAYAN	SABA	75%
EHELIYAGODA	NIMESH	SABA	45%
EHELIYAGODA	DILIP	SABA	40%

EHELIYAGODA	Total		165%
EMBILIPITIYA	KUMAR	SOU	60%
EMBILIPITIYA	DINESH	SOU	50%
EMBILIPITIYA	SILVA	SOU	15%


EMBILIPITIYA	Total		145%
RATNAPURA	NIMAL	SABA	29%
RATNAPURA	HIMAN	SABA	25%
RATNAPURA	PIYAL	SABA	15%

Re: Highlighting first highest 3 under each Branch

Brett_Bleess — Wed, 15 Jul 2020 13:34:31 GMT

@sunny_talwar any chance you can circle back and have a last look at the latest post? Sorry for the bother, but I am not as good as you are at these types of questions. If anyone else that is a better developer than me has a minute and can check, appreciate it. Given it is groupings is the part that is throwing me. Am assuming may need to use AGGR in things, but not sure, so I guess I can toss out the following Design Blog post, as that might help:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett