topic Re: How to use firstsortedvalue in a dimension in QlikView

How to use firstsortedvalue in a dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

sunny_talwar — Tue, 24 Jul 2018 00:10:15 GMT

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

qlikviewwizard — Tue, 24 Jul 2018 03:25:34 GMT

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

];

Re: How to use firstsortedvalue in a dimension

qliksus — Tue, 24 Jul 2018 05:52:14 GMT

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

fabdulazeez — Tue, 24 Jul 2018 06:22:45 GMT

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

Re: How to use firstsortedvalue in a dimension

Anonymous — Tue, 24 Jul 2018 16:42:49 GMT

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

Anonymous — Tue, 24 Jul 2018 16:44:43 GMT

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

Anonymous — Tue, 24 Jul 2018 16:47:27 GMT

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

sunny_talwar — Tue, 24 Jul 2018 16:54:23 GMT

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

Anonymous — Tue, 24 Jul 2018 18:21:56 GMT

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

Re: How to use firstsortedvalue in a dimension

vishsaggi — Tue, 24 Jul 2018 18:37:53 GMT

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

Anonymous — Tue, 24 Jul 2018 18:51:46 GMT

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

vishsaggi — Tue, 24 Jul 2018 19:14:15 GMT

Share your sample qvw file where you having issues with.

Re: How to use firstsortedvalue in a dimension

sunny_talwar — Tue, 24 Jul 2018 19:18:48 GMT

I agree with vishsaggi‌, 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

Anonymous — Tue, 24 Jul 2018 22:14:27 GMT

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

sunny_talwar — Tue, 24 Jul 2018 23:56:23 GMT

Try this

Dimension

SalesPerson

Expression

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

Re: How to use firstsortedvalue in a dimension

Anonymous — Wed, 25 Jul 2018 00:15:40 GMT

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

Re: How to use firstsortedvalue in a dimension

sunny_talwar — Wed, 25 Jul 2018 00:57:37 GMT

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

Re: How to use firstsortedvalue in a dimension

fabdulazeez — Wed, 25 Jul 2018 03:52:52 GMT

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