Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ishanbhatt
Creator II
Creator II

FirstSortedValue Function with Aggr

Hello All,

Using FirstSortedValue and Aggr functions we can found the specific string from our data set.

Let's start with a simple example.

Sales:

CountrySales
US2000
China1500
India2500
Japan1000
Canada4000

Now I want to found which country has highest sales? For this, We can use FirstSortedValue with Aggr function.

!

=FirstSortedValue([Country],-aggr(Sum([Sales]),[Country]),1)


Let's understand the expression: 

  1. Aggr(Sum(Sales), Country): Finding Sum of Sales aggregated by Country.
  2. FirstSortedValue([Country], -aggr(Sum9Sales),[Country]),1): Now We need the country name based on higest sales. So, We've used Country field in FirstSortedValue function.
    • Minus(-) Sign: if we did not put minus sign then upper expression give the lowest sales country name.
    • Number (rank): put your desired number rank in the last.

So Expression gives the answer: Canada


Some More Example:


1. FirstSortedValue([Country],-aggr(Sum([Sales]),[Country]),2)

           Answer : India

2. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),1)  //Remove Minus sign

          Answer: Japan

3. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),2)  //Remove Minus sign

          Answer: China

Labels (1)
3 Replies
jgreen95
Contributor III
Contributor III

it possible to return the actual value as well as the Country? 

Once i know the country with the top sales i want to know the value of that as well.

lakhansajwan
Contributor III
Contributor III

IN this example you dont need to do the Aggr inside the FirstSortedValue function as we have single sales value for a country for this example even if we pass sales dimension instead of Aggr function it will give us the correct result as desired.

You would require the Aggr function when we are having multiples vales of sales for a single country and then you wanted to do the group by Sales  Country Wise, as this will give me single sum value for each country.

Hope this clear the things more 

Almen
Creator
Creator

@jgreen95 

Just use Max([ValueField]) or if you have multiple values MAX(AGGR(SUM([ValueField]), [GroupField])).