Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
maybe this:
dimensions:
expression:
max(aggr(if(count([Work Asset Owned]) >1, count([Work Asset Owned])), Customer, [Work Asset Owned]))
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]))
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