Don't get aggr()avated using aggr()

[Michael_Tarallo]

Hi guys, in this video edition of the Qlik Design Blog, I cover a well known but often misunderstood chart function known as Aggr(). Aggr() is used in advanced calculations when you need to make a second aggregation based on the result of the first. Such as which customer in which country had the most orders. Note that in combination with AGGR you can also use the FirstSortedValue() function with your aggregated measure as an argument, in order to display the dimension along with the aggregated measure. I provided examples below. Enjoy!

Video Link : 5968

Let's Break It Down

Spotting the difference and piecing it all together so you can see how it works is a great way to internalize the function. This can be done with either Qlik Sense or QlikView. I put this together in a manner that allows you to see the breakdown of how aggr() works in conjunction with the video.

Which customers had the most orders?

• Use a table object
• Add Customer as dimension
• Add Count(OrderID) as measure
• In the table click the header column to sort - Descending - Customer on top had the most orders

Which countries had the most orders?

• Use a table object
• Add Country as dimension
• Add Count(OrderID) as measure
• In the table click the header column to sort - Descending - Country on top had the most orders
  
  

Which customers, in what countries has the most orders?

• Create a table object
• Add Country as dimension
• Add Customer as dimension
• Add Count(OrderID) as measure
• Hmmmm.... that's not right - it shows me the number of orders within each customer for each country, not the most.
• Ah - if I use Aggr() with my expression - I can use the Max() aggregation to get the most of the order count for each customer as in:
  • max(aggr(Count(OrderID),Country,CustomerName)

BONUS Feature: FirstSortedValue()

• But how do I display the corresponding customer value in that country?
  If I just use Country in the table, the results are similar to my previous example and are not correct.
  • Ah - if I create a measure using the FirstSortedValue() function along with my aggr() expression I can display the Max customer in that country in my table.
    
    
    • FirstSortedValue(CustomerName,-Aggr(Count(DISTINCT OrderID),Country, CustomerName))

FirstSortedValue( value, sort_weight, rank 😞

FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument. So CustomerName is the value I want to display or return in the table and -aggr(Count(Distinct OrderID),Country,CustomerName) is the sort_weight argument...where " - " is used to denote the highest (or most) - the number of orders. This returns the value of the customer corresponding to the most orders for that customer.

The result - a single consolidated table showing you the customers with the most orders in their corresponding countries.

Which Customer in what Country had the most orders

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

Resources:

• Qlik Help
• QlikView Technical Brief - AGGR.docx
• Aggr()
• FirstSortedValue
• Sample attached (copy.qvf to your C:\Users\<user>\Documents\Qlik\Sense\Apps if you using Qlik Sense Desktop or import to Qlik Sense Enterprise using the QMC, or upload to Qlik Sense Cloud.

Aggr.qvf

[narband2778]

Hi Mike,

I think theFirstSortedValue() used in Sample attached should be as below:

FirstSortedValue(CustomerName,-Aggr(Count(DISTINCT OrderID),Country, CustomerName))

Thanks,

Naresh

[Michael_Tarallo]

Hello Naresh - Thank You! - I recall using this in the video, but when creating the example I used another expression - You are absolutely correct. I will make the change. When using both expressions - I did get the similar results - but then noticed there is a slight difference in the max countries - do note that when using this expression if there are two dimension values with the same rank value it will return a " - " as per the documentation. Thanks again!

".... NULL, because there are two values of Customer (El Carnivale and Roba di Piel) with the same rank of order count. Use the distinct qualifier to make sure unexpected null results do not occur."

[jnolanhcf]

Hi

Finding the null ambiguous, I tried

if(isnull(FirstSortedValue(CustomerName,-Aggr(Count(DISTINCT OrderID),Country, CustomerName))),'multiple results',FirstSortedValue(CustomerName,-Aggr(Count(DISTINCT OrderID),Country, CustomerName)))

Not pretty, but clearer for user.

BTW I get an error "Unable to connect to the content" when trying to watch the video.

Cheers

Judith

[JonasValleskog]

Hi Mike,

I thought I'd experiment with overcoming the remaining possible requirement of showing customer records where there are two or more customers with the highest rank. I.e. to get rid of your null result for Brazil. The following should do the trick.

concat(DISTINCT

aggr(

    if(rank(Count(DISTINCT OrderID))=1,CustomerName)

  ,Country

  ,CustomerName

),' , '

)

Cheers

Jonas

[Michael_Tarallo]

Hi Guys - thanks for the additional examples - in regards to the video - it requires Flash to be enabled in the browser. I updated the one on YouTube here: Qlik Sense in 60 - The Aggr() function - YouTube - just in case your browser is not working with Adobe FLASH.