18 Replies Latest reply: Jul 24, 2018 11:52 PM by Fawaz Abdul Azeez

# How to use firstsortedvalue in a dimension

Hi all, this is my first post.

Need help trying to limit a dimension. I have created a chart and limit the chart to only show sales people in the top volume region.

I can find my top region by using =FirstSortedValue( region , -aggr( count(ordernumber), region ) )

But i don't know how to limit this to only show sales people in the top region. any help would be appreciated.

• ###### Re: How to use firstsortedvalue in a dimension

May be this as your calculated dimension

Aggr(If(Count(ordernumber) = Max(TOTAL Aggr(Count(ordernumber), region), region), region)

• ###### Re: How to use firstsortedvalue in a dimension

Hi Sunny,

I need to display all of the sales people in the top region though. I don't believe this would do it.

• ###### Re: How to use firstsortedvalue in a dimension

May be this

Aggr(If(Count(ordernumber) = Max(TOTAL <region> Aggr(Count(ordernumber), region), SalesPerson), region, SalesPerson)

• ###### Re: How to use firstsortedvalue in a dimension

Hi Sunny, I tried that to and getting an error in expression

• ###### Re: How to use firstsortedvalue in a dimension

Just close the bracket for Max() function. Its just a typo.

Aggr(If(Count(ordernumber) = Max(TOTAL <region> Aggr(Count(ordernumber), region)), SalesPerson), region, SalesPerson)

• ###### Re: How to use firstsortedvalue in a dimension

thanks Vishwarath. the equation works but its not displaying the correct chart.

let me explain the background. We have multiple states broken down into multiple regions with multiple salespeople in each region.

Based on the filters (state, month), my chart needs to dynamically always show the top region with all of the sales people in that region and how many orders they completed.

• ###### Re: How to use firstsortedvalue in a dimension

Share your sample qvw file where you having issues with.

• ###### Re: How to use firstsortedvalue in a dimension

I agree with Vishwarath, it might help to see what you have in order to trouble shoot it... a sample qvw or even raw data might help in figuring out what you are after....

• ###### Re: How to use firstsortedvalue in a dimension

Hi guys, thank you so much for your patience.

This is the table it would pull from and the chart that it should look like.

• ###### Re: How to use firstsortedvalue in a dimension

Try this

Dimension

SalesPerson

Expression

=Count({<Region = {"=Rank(Count(OrderID)) = 1"}>}OrderID)

• ###### Re: How to use firstsortedvalue in a dimension

Thank you so much Sunny and team. That did the job!

• ###### Re: How to use firstsortedvalue in a dimension

Awesome , I am glad we finally managed to get it resolved

• ###### Re: How to use firstsortedvalue in a dimension

Hi Quy Tran ,

Please check Sunny Talwar 's solution with small correction.

Data:

ordernumber,region,sales

1,R1,10

2,R2,20

3,R3,4

4,R4,5

11,R4,20

12,R1,50

14,R2,20

20,R1,20

21,R1,4

22,R2,5

23,R1,20

24,R2,4

25,R3,5

];

• ###### Re: How to use firstsortedvalue in a dimension

If displaying one region is what you always  want then just create 2 expressions like the below

Exp1 :  FirstSortedValue( region , -aggr(count(ordernumber),region))

Exp2:  sum({<region={\$(=FirstSortedValue( region , -aggr(count(ordernumber),region)))}>}sales)

• ###### Re: How to use firstsortedvalue in a dimension

Hi Susant, sorry i wasnt being clear. i need to show all of the sales people in that region with a count of all of their sales.

• ###### Re: How to use firstsortedvalue in a dimension

Count({<region= {"=rank(Count(ordernumber),4)<= 10"}>} ordernumber)

• ###### Re: How to use firstsortedvalue in a dimension

Hi Fawaz, Sorry I wasn't being clear. i need to display all of the sales people in that top region and a count of all of their sales.

• ###### Re: How to use firstsortedvalue in a dimension

Its fine. I should have added more explanation so you could derive at the answer