Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
Try this.
Sum(Aggr(Max({<[Gender/Age Group]=>} TOTAL <Session> [Value]), Session, [Gender/Age Group]))