Set analysis, FirstSortedValue & Aggr

[JulienQ]

Hi,

Sorry, it seems trivial, i read lot of topic, but i feel lost.

I have containers that contains categorized objects :

container	object_id	cat
C1	1	A
C1	2	B
C1	3	A
C1	4	A
C2	5	B
C2	6	H
C2	7	H
C2	8	D
C3	9	E
C3	10	E
C3	11	A
C3	12	H
C3	13	E

I want the largest category for each container, it's ok :

=FirstSortedValue( cat, -aggr( count(object_id), container, cat))

container	=FirstSortedValue( cat, -aggr( count(object_id), container, cat))
C1	A
C2	H
C3	E

But if i use that in set analysis to count objects :  count( {<cat={"$(=  here )"} object_id) )

it doesn't work :

=count( {<cat={"$(=FirstSortedValue( cat, -aggr( count(object_id), container, cat)))"}>} object_id)

container	=FirstSortedValue( cat, -aggr( count(object_id), container, cat))	=count( {<cat={"$(=FirstSortedValue( cat, -aggr( count(object_id), container, cat)))"}>} object_id)
C1	A	0
C2	H	0
C3	E	0

(note : i'm not admin, unable to edit loading script)

Thanks for your help.

[Oleg_Troyansky · Accepted solution]

Hi Julien,

Kudos to you - you are tackling very complex calculations for a newbie!

So, this calculation doesn't work in Set Analysis because it needs to be "sensitive" to the dimensions of your chart (such as Container), but Set Analysis cannot do that because it's evaluated once per chart, not once for each line of your chart.

The good news is that you don't need to insert this formula into your Set Analysis. You can simply use nested aggregation to calculate the max value of the count of objects for the highest category. As simple as this:

max(aggr(count(object_id), container, cat))

Let me invite you to my upcoming on-line session on Advanced Set Analysis and AGGR on November 18th. I will be teaching advanced analytical uses of Set Analysis and AGGR, and one of our hands-on examples deals exactly with your situation. You will learn a lot, I promise! See direct link in my signature below.

[JulienQ]

Oleg,

Thank you so much. It was so obvious... you allowed me to step back.

In fact, the final goal it's to filter rows to keep only largest category by containers.

Based on your solution, this seems to be working :

=if(
	max(aggr(nodistinct count(object_id), container, cat))
    =
	max(total <container> aggr(count(object_id), container, cat))
    ,
    'keep'
)

(But i need to think about why  😅)

Thank you once again

[Oleg_Troyansky]

Hi Julien,

You seem to be hitting many of the exercises from my Set Analysis and AGGR class, one by one. There is a number of possible solutions for limiting the table to N top performing elements. The one you came up with, is working, but I could offer a simpler one.

I can give you a hint - in your calculated dimension for a Category, use a single AGGR and a function Rank() to limit categories to only top ones per container.

I really hope that you can make it to my class on November 18th, you'll enjoy it!

Cheers,