Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
just try either of the below
=Rank(sum(measure), 4, 1)
OR
=aggr( Rank(sum(measure), 4, 1), DoctorName, District)
@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?
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.
Hi,
Try this one
=Rank(sum(Count))
This will work prefectly.
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;
It works thank you! Is it possible to achieve this in Chart Expressions?
just try either of the below
=Rank(sum(measure), 4, 1)
OR
=aggr( Rank(sum(measure), 4, 1), DoctorName, District)
@yh2023 This should work.
=Aggr(Rank(Sum(Count),District),DoctorName, District)
Great it works Thank you!