Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank - Exclude Column while ranking

Hi everyone,

I have following data

LOAD * INLINE [
    Region, Name, Sales
    South, A, 2000
    South, B, 4000
    South, A, 3000
    North, A, 3000
    North, B, 2000
    West, A, 1000
    West, B, 3000
]
;

Created a straight chart like below:

RegionNameTotal SalesRank
WestA10002
WestB30001
NorthA30001
NorthB20002
SouthA50001
SouthB40002
18000-

Ranking is done region wise. I need to rank on the basis of Name only.

The chart should show the result like:

Region

Name

Total Sales

Rank

South

A

5000

1

South

B

4000

2

West

B

3000

3

North

A

3000

3

North

B

2000

4

West

A

1000

5

18000

-

Thanks in advance.

1 Solution

Accepted Solutions
Sokkorn
Master
Master

Hi,

Maybe script level can do this job

[Data]:

LOAD * INLINE [

Region, Name, Sales

    South,A, 2000

    South,B, 4000

    South,A, 3000

    North,A, 3000

    North,B, 2000

    West,A, 1000

    West,B, 3000

];

[tmpRank]:

LOAD

    Region        AS [Region2],

    Name        AS [Name2],

    Sum(Sales)    AS [Sales2]

Resident [Data] Group By Region,Name;

[Rank]:

LOAD

    RecNo()    AS [No],

    *,

    IF([Sales2] <> Previous([Sales2]), RangeSum(1, Peek('Rank')),Peek([Rank])) AS [Rank]

Resident [tmpRank] Order By [Sales2] Desc;

DROP Table [tmpRank]

Regards,

Sokkorn

View solution in original post

8 Replies
Sokkorn
Master
Master

Hi,

Have you try this yet: Rank(Total Sum(Sales))

Rgds,

Sokkorn

Not applicable
Author

just tried giving answer like

RegionNameTotal SalesRank
WestA10006
WestB30003
NorthA30003
NorthB20005
SouthA50001
SouthB40002
18000-
Sokkorn
Master
Master

How do you think? Correct or not?

Not applicable
Author

To show top 3 salesman

I used below domension (2nd) 1st is region

 

=if(aggr(Rank(Total Sum(Sales),0,0),Sales)<=3 ,Name ,null())

result is

RegionNameTotal SalesRank
NorthA30003
SouthA50001
SouthB40002
12000-

does not show

West B 3000

if i used

=if(aggr(Rank(Total Sum(Sales),0,0),Sales)<=4 ,Name ,null())

result is not correct

RegionNameTotal SalesRank
WestA10004
NorthA30003
SouthA50001
SouthB40002
13000-

Sokkorn
Master
Master

Hi,

Maybe script level can do this job

[Data]:

LOAD * INLINE [

Region, Name, Sales

    South,A, 2000

    South,B, 4000

    South,A, 3000

    North,A, 3000

    North,B, 2000

    West,A, 1000

    West,B, 3000

];

[tmpRank]:

LOAD

    Region        AS [Region2],

    Name        AS [Name2],

    Sum(Sales)    AS [Sales2]

Resident [Data] Group By Region,Name;

[Rank]:

LOAD

    RecNo()    AS [No],

    *,

    IF([Sales2] <> Previous([Sales2]), RangeSum(1, Peek('Rank')),Peek([Rank])) AS [Rank]

Resident [tmpRank] Order By [Sales2] Desc;

DROP Table [tmpRank]

Regards,

Sokkorn

Not applicable
Author

Thanks a Lot Sokkorn

Ranking was done correctly

but not showing Salesman according to descending order of sales

Region2Name2RankSales2
-
SouthA15000
SouthB24000
NorthA33000
NorthB42000
WestA51000
WestB33000
Not applicable
Author

Thanks alot ...

I user No for sorting

It worked

brijesh1991
Partner - Specialist
Partner - Specialist

Hey since my expression is too long and is combination of 3 other expressions, Can I do it on front end??