Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
wanyunyang
Creator III
Creator III

Get rank Regardless of some selections

Hi all,

 

I have expression like: 

aggr(rank(Sales,4,1),Order)

 

I also have two filters: Date and Salesrep. 

 

I would like to let the rank expression not change with Salesrep. How should I write the expression?

Thanks in advance!

1 Solution

Accepted Solutions
sunny_talwar

May be this

Only({<Salesrep>} Aggr(Rank(Only({<Salesrep>} Sales), 4, 1), Order))

View solution in original post

7 Replies
skamath1
Creator III
Creator III

Try 

aggr( {1}   rank(Sales,4,1),Order)

 

or 

aggr ( {1} rank( TOTAL Sales,4,1), Order)

sunny_talwar

May be this

Only({<Salesrep>} Aggr(Rank(Only({<Salesrep>} Sales), 4, 1), Order))
ckmchinmaya
Contributor III
Contributor III

Hi Sunny,

I have an exact problem working on a dataset, i have tried this solution but i believe i couldn't catch it.

I have the below objective ( it shouldn't respond to any filters like Operators & Domains

1) Getting Rank of my operator ( for a particular domain )

2) If my organization is not ranked 1st then I need who ranked 1st for that particular domain and what is the gap

Solution : what i was doing i have created a variable in script

LET Q2_value = 'count({<domain={''Voice''},operator=>}if (Qualifier_1=''QUALIFIED'',Qualifier_1))/Count({<domain={''Voice''},operator=>}Qualifier_1)';

Then I am trying to get the rank ( the query is working fine - but it responds to the filter - because of the IF statement )

=Aggr(if(operator='XX' and domain='Voice',rank(aggr({<domain='Voice'}>}$(Q2_value),domain,operator))),domain,operator)

I think i am over complicating this - but I need some help to get this sorted.

Thanks 

 

 

 

 

 

sunny_talwar

May be share a sample where we can see what you have

ckmchinmaya
Contributor III
Contributor III

Hi,

The dataset looks like this. For each domain, I am going to show my organisation "XX" Rank, If the rank is not 1 for any domain I need to show who is 1st and how much we are trailing from Rank 1.

I will be showing all these in different text boxes part of executive summary.

domainoperatorKPI
FDLXX98.76%
FDLOO96.72%
FDLTT95.69%
FDLVD97.75%
FDT_DLXX98.58%
FDT_DLOO95.96%
FDT_DLTT94.53%
FDT_DLVD97.31%
FDT_ULXX97.88%
FDT_ULOO95.61%
FDT_ULTT96.61%
FDT_ULVD96.61%
FULXX98.46%
FULOO96.68%
FULTT97.50%
FULVD97.12%
VoiceXX95.81%
VoiceOO95.52%
VoiceTT95.90%
VoiceVD94.79%
sunny_talwar

I don't believe I follow the question well enough to answer it. Can you provide how the output needs to look like based on the data provided?

ckmchinmaya
Contributor III
Contributor III

I am attaching a screenshot from dashboard. Please let me know - if you need anything else.

This is for domain "Voice".

sample.png