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: 
Anonymous
Not applicable

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.

18 Replies
sunny_talwar

May be this as your calculated dimension

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

qlikviewwizard
Master II
Master II

Hi qtran2018 ,

Please check stalwar1 's solution with small correction.

Data:

LOAD * INLINE [

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

];

1.PNG

2.PNG

qliksus
Specialist II
Specialist II

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)

fabdulazeez
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

Hi Sunny,

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

sunny_talwar

May be this

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

Anonymous
Not applicable
Author

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