New to QlikView

Discussion board where members can get started with QlikView.

New Contributor

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.

Tags (3)
18 Replies
MVP

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)

Honored Contributor II

Re: How to use firstsortedvalue in a dimension

Hi qtran2018 ,

Please check stalwar1 '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

];

Valued Contributor

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)

Contributor

Re: How to use firstsortedvalue in a dimension

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

New Contributor

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.

New Contributor

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.

New Contributor

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.

MVP

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)

New Contributor

Re: How to use firstsortedvalue in a dimension

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