Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
rinalldo
New Contributor

Aggr Function

Hi everyone

I have a question for you

For my learning, I have a dummy table of customer Asset list (very simple table)

 

Customer | Work Asset Owned

Alan | Phone

Alan | Phone

Alan | PC Desktop

Alan | PC Laptop

Bernard | Phone

Bernard | PC Desktop

Bernard | PC Laptop

Bernard | PC Laptop

Bernard | PC Laptop

Cindy | Phone

Cindy | PC Desktop

 

My objective is to obtain the information about the most number of Asset owned by someone. So in this case, when building Pivot table:

1. For Alan, I want to see Phone (2) because he has 2 phones (which is the highest count of all assets he owns)

2. For Bernard, I want to see PC Laptop (3) because he has 3 PC Laptops

3. For Cindy, it can be anything since she only has 1 each (no Max value)

 

I am still learning and I think I have to use Aggr function, but I am not sure how.

Would anyone be able to help me?

Labels (2)
3 Replies
zhadrakas
Valued Contributor II

Re: Aggr Function

maybe this:

dimensions:

  •  Customer
  • Work Asset Owned

expression: 

max(aggr(if(count([Work Asset Owned]) >1, count([Work Asset Owned])), Customer, [Work Asset Owned]))
Highlighted

Re: Aggr Function

Is this what you are trying to get?

image.png

Expression used

Sum(Aggr(If(Max(TOTAL <Customer> Aggr(Count([Work Asset Owned]), Customer, [Work Asset Owned])) = Count([Work Asset Owned]), Count([Work Asset Owned])), Customer, [Work Asset Owned]))
Highlighted
Digital Support
Digital Support

Re: Aggr Function

Did either of the posts assist you with your use case questions?  If so, do not forget to return and use the Accept as Solution button on the post(s) that helped, as this gives the posters credit for the assistance and lets other Community Members know what actually worked.  If you require further assistance, please leave an update post.

Here are some specific Design Blog posts that may be helpful, and the last post will be the base URL to the Design Blog area, you can use that to further search yourself, there are hundreds of mostly how-to posts in this area by our internal experts on the different products...

https://community.qlik.com/t5/Qlik-Design-Blog/Use-Aggregation-Functions/ba-p/1475833

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-get-aggr-avated-using-aggr/ba-p/1464136

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Hopefully this is further help...

Regards,
Brett

 

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.