topic Re: Custom grouping, aggr expression problem in App Development

Custom grouping, aggr expression problem

RaduM — Mon, 06 Dec 2021 10:35:34 GMT

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

Re: Custom grouping, aggr expression problem

brunobertels — Mon, 06 Dec 2021 17:36:02 GMT

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 :

Re: Custom grouping, aggr expression problem

RaduM — Mon, 06 Dec 2021 18:06:35 GMT

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

Thanks again

Re: Custom grouping, aggr expression problem

brunobertels — Tue, 07 Dec 2021 09:28:55 GMT

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.

Re: Custom grouping, aggr expression problem

RaduM — Tue, 07 Dec 2021 10:53:30 GMT

i like this solution better