topic Re: Aggr Function in QlikView

Aggr Function

rinalldo — Sat, 16 Nov 2024 01:39:18 GMT

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 | PC Desktop

Alan | PC Laptop

Bernard | Phone

Bernard | PC Desktop

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?

Re: Aggr Function

zhadrakas — Fri, 20 Dec 2019 07:24:19 GMT

maybe this:

dimensions:

Customer
Work Asset Owned

expression:

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

Re: Aggr Function

sunny_talwar — Fri, 20 Dec 2019 13:34:47 GMT

Is this what you are trying to get?

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]))

Re: Aggr Function

Brett_Bleess — Thu, 26 Dec 2019 14:39:44 GMT

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