Ranking Based on District Counts within DoctorName Groups

[yh2023]

Hey everyone,

I'm trying to create a ranking based on the count of each District within individual DoctorName groups. Here's a sample table structure:

DoctorName | District | Count
A1 | Hyd | 21
A1 | Karm | 20
A1 | Goa | 70
A2 | Hyd1 | 45
A2 | Karm1 | 69
A2 | Goa | 1

I'm using the Rank function to generate the ranking based on the count of each District within each DoctorName group, ordered by decreasing count.

I want the output column to be
DoctorName | District | Count | Output
A1 | Hyd | 21 | 2
A1 | Karm | 20 | 3
A1 | Goa | 70 | 1
A2 | Hyd1 | 45 | 2
A2 | Karm1 | 69 | 1
A2 | Goa | 1 | 3

I tried - 
=aggr(Rank(total sum(Count), 1, 1), DoctorName, District)

But this gives the output as - 
DoctorName | District | Count | Output
A1 | Hyd | 21 | 4
A1 | Karm | 20 | 5
A1 | Goa | 70 | 1
A2 | Hyd1 | 45 | 3
A2 | Karm1 | 69 | 2
A2 | Goa | 1 | 6

Any insights or suggestions on how to refine this approach or achieve the desired output would be greatly appreciated!

[Anil_Babu_Samineni]

@yh2023  Let's focus 

DoctorName | District | Count | Output
A1 | Hyd | 21 | 2
A1 | Karm | 20 | 3
A1 | Goa | 70 | 1
A1 | Hyd1 | 45 | 2
A1 | Karm1 | 69 | 1
A1 | Goa | 1 | 3

what logic this should be 1 for bold? Can you explain? Why you are repeasting same Rank 2 times?

[yh2023]

@Anil_Babu_Samineni 

I appreciate the clarification; it should be A2.

DoctorName | District | Count | Output
A1 | Hyd | 21 | 2
A1 | Karm | 20 | 3
A1 | Goa | 70 | 1
A2 | Hyd1 | 45 | 2
A2 | Karm1 | 69 | 1
A2 | Goa | 1 | 3

Now, I'm looking to generate rankings based on the 'Count,' but I want a distinct ranking for each 'District' within every 'DoctorName' group.

[Parthiban]

Hi,

Try this one

=Rank(sum(Count))

This will work prefectly.

[Ahidhar]

try this in script

tab:
load * inline
[
DoctorName | District | Count
A1 | Hyd | 21
A1 | Karm | 20
A1 | Goa | 70
A2 | Hyd1 | 45
A2 | Karm1 | 69
A2 | Goa | 1
]
(delimiter is '|');

NoConcatenate
tab1:
load *,
autonumber(Count,DoctorName) as Output
resident tab order by DoctorName,Count desc;
drop table tab;

[yh2023]

It works thank you! Is it possible to achieve this in Chart Expressions?

[vinieme12 · Accepted solution]

just try either of the below

=Rank(sum(measure), 4, 1)

OR

=aggr( Rank(sum(measure), 4, 1), DoctorName, District)

[Anil_Babu_Samineni]

@yh2023 This should work.

=Aggr(Rank(Sum(Count),District),DoctorName, District)

[yh2023]

Great it works Thank you!