sum with max for multiple columns

[nancysh]

hi All, 

I have a question about calculating max and sum i have this data: 

session 1						session 2						session 3
male 5-17	male 18-49	male 50+	female 5-17	female 18-49	female 50+	male 5-17	male 18-49	male 50+	female 5-17	female 18-49	female 50+	male 5-17	male 18-49	male 50+	female 5-17	female 18-49	female 50+
2	0	3	1	0	0	1	3	0	0	0	0	0	0	0	4	0	5
0	0	3	3	0	0	0	1	4	1	2	2	1	1	1	0	1	1

and i need to calculate number of people attended sessions without double counting so i need the max for ech age/gender group then sum for all.

 i need to get the data as total but the max should be for each row then sum all rows.

can anyone help me with formula.

Thanks.

[starke_be-terna]

What you are asking for is a guess at best, when you do not have any identifiers for each individual person. There are multiple possible scenarios:
1) everyone is unique (so the 2 people in group 1 session 1 are different to the 1 person in group 1 session 2) --> the sum of all elements would be correct
2) if there are people in multiple sessions, then these people were in all sessions --> max would be correct assumption
3) People could be mix and match between 1) and 2) --> only count(distinct PERSON) would lead to a satisfying result.

About 2). You could do this with aggr() by first evaluating max() by age_group and then sum the results up:
sum(aggr(max(PERSON_COUNT), AGE_GROUP))

[Chanty4u]

Try this.

Sum(Aggr(Max({<[Gender/Age Group]=>} TOTAL <Session> [Value]), Session, [Gender/Age Group]))