Get rank Regardless of some selections

[wanyunyang]

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!

[skamath1]

Try 

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

or 

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

[sunny_talwar · Accepted solution]

May be this

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

[ckmchinmaya]

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]

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.

domain	operator	KPI
FDL	XX	98.76%
FDL	OO	96.72%
FDL	TT	95.69%
FDL	VD	97.75%
FDT_DL	XX	98.58%
FDT_DL	OO	95.96%
FDT_DL	TT	94.53%
FDT_DL	VD	97.31%
FDT_UL	XX	97.88%
FDT_UL	OO	95.61%
FDT_UL	TT	96.61%
FDT_UL	VD	96.61%
FUL	XX	98.46%
FUL	OO	96.68%
FUL	TT	97.50%
FUL	VD	97.12%
Voice	XX	95.81%
Voice	OO	95.52%
Voice	TT	95.90%
Voice	VD	94.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]

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

This is for domain "Voice".