topic Re: first sorted value and aggr in QlikView

first sorted value and aggr

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

hi all,

i cant seem to work out why the below wont work

=FirstSortedValue(aggr(sum({$< Area -= {''}>}[Total Emissions]),Area),Area)

basically the aggr creates a virtual table of total emissions grouped by area (this works ive tested it separately). i then want to sort that table such that i return the area with the highest total NOx.

any help is much apprecoated/

thanks

Re: first sorted value and aggr

rahulpawarb — Mon, 07 Aug 2017 11:36:44 GMT

Could you please share sample application? this will help us to provide expected inputs.

Regards!

Rahul Pawar

Re: first sorted value and aggr

balar025 — Mon, 07 Aug 2017 11:36:50 GMT

Any Sample data or app?

Re: first sorted value and aggr

antoniotiman — Mon, 07 Aug 2017 11:46:45 GMT

Hi Sam,

maybe this

FirstSortedValue(DISTINCT Area,-Aggr(Sum([Total Emissions]),Area))

or provide sample data.

Regards,

Antonio

Re: first sorted value and aggr

Anil_Babu_Samineni — Mon, 07 Aug 2017 11:47:54 GMT

I think you missed first parameter

Re: first sorted value and aggr

sunny_talwar — Mon, 07 Aug 2017 11:56:57 GMT

Try this

=FirstSortedValue({$<Area -= {''}>} Area, -Aggr(Only({$<Area -= {''}>} Area), Area))

Re: first sorted value and aggr

samuel_brierley — Mon, 07 Aug 2017 12:19:25 GMT

Hi all,

thanks for the suggestions but they dont seem to be working quite yet.

i've attached a sample for your reference.

Re: first sorted value and aggr

sunny_talwar — Mon, 07 Aug 2017 12:23:02 GMT

Try this

=FirstSortedValue({$< Area -= {''}>} Area,-Aggr(Sum({$< Area -= {''}>}[Total Emissions]),Area))

Re: first sorted value and aggr

samuel_brierley — Mon, 07 Aug 2017 12:26:36 GMT

excellent thank you!!!

Re: first sorted value and aggr

sasiparupudi1 — Mon, 07 Aug 2017 12:27:32 GMT

Swap the aggr and area dimensions

FirstSortedValue(Distinct Area,-aggr(sum({$< Area -= {''}>}[Total Emissions]),Area))

Re: first sorted value and aggr

anushree1 — Wed, 27 Feb 2019 09:06:22 GMT

Hi Sunny,

I happened to check this thread and seemed to have seen something different here the Expression:

FirstSortedValue({$< Area -= {''}>} Area,-Aggr(Sum({$< Area -= {''}>}[Total Emissions]),Area))

Very first Parameter into FirstSortedValue Function which is usually a Dimension is defined by a Set Analysis Expression but not encapsulated with any aggr function , so could you please spare some time and explain the situation here.

I have changed the font of the Parameter in the expression to emphasize what exactly is my question