Custom grouping, aggr expression problem

[RaduM]

hi,

i have an example loading data to illustrate my problem

load * Inline [

Year,Month,Name,Group,SubGroup,Value
2021,8,Name1,Group1,Subgroup1,10
2021,8,Name2,Group1,Subgroup1,8
2021,8,Name3,Group1,Subgroup2,10
2021,8,Name4,Group1,Subgroup3,7
2021,8,Name5,Group1,Subgroup1,5

2021,8,Name6,Group2,Subgroup1,8
2021,8,Name7,Group2,Subgroup1,6
2021,8,Name8,Group2,Subgroup2,9
2021,8,Name9,Group2,Subgroup3,4
2021,8,Name10,Group2,Subgroup1,6

2021,9,Name1,Group1,Subgroup1,10
2021,9,Name2,Group1,Subgroup1,8
2021,9,Name3,Group1,Subgroup2,10
2021,9,Name4,Group1,Subgroup3,7

2021,9,Name6,Group2,Subgroup1,8
2021,9,Name7,Group2,Subgroup1,6
2021,9,Name8,Group2,Subgroup2,9
2021,9,Name9,Group2,Subgroup3,4
2021,9,Name10,Group2,Subgroup1,6
2021,9,Name11,Group2,Subgroup2,4

]

i created a table with 3 dimensions and 2 expressions

the first expression is the sum of values

and the second one should be the number of people with the same group and subgroup in the current selection

I do not understand why I get the correct value only for the first Name in each group and subgroup and not for all of them.

please let me know how I can fix this problem

thanks

 

[brunobertels · Accepted solution]

Hi 

try this 

aggr( nodistinct aggr( count( distinct Name), Group,SubGroup), Group,SubGroup)

the trick is to first aggr by dim the mesure count(distinct Name) 

Then to populate the value in all cell 

"Re" aggr this result this time with a no distinct :

[RaduM]

Thank you so mutch. I need to understand the solution now. If you can please explain more i will apreciate.

Thanks again

[brunobertels · Accepted solution]

hi 

In fact this formula will do the same 

aggr( NODISTINCT count( distinct Name), Group,SubGroup)

So :

DISTINCT will return one result for each value upon which aggregation is applied.

NODISTINCT will return an array of values as a result for each value upon which aggregation is applied.

[RaduM]

i like this solution better