Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Sum values greater than 1 of distinct users

Hi,

I have this table:

 YEAR TOTAL EXAMS 2017 5.327 2019 5.943 2018 6.342 17.612

However, the number of exams should only be calculated for users who have taken >1 exam. That is, the sum must be from the second exam, users who have taken 1 exam are eliminated from the analysis.

My dimension is year and I have 2 exam codes, if the user has taken 1 exam in code 40302733 and another one in 40302075, he must total 2 exams. And now, my expression is considering even who took 1 exam.

IF(SUM({\$<USER = {"NORMAL"}, COD_EXAM ={"40302733","40302075"},AGE={">=19"}*{"<=75"}>} TOTAL_EXAM) > 1 ,
SUM({\$<USER = {"NORMAL"}, COD_EXAM = {"40302733","40302075"},AGE={">=19"}*{"<=75"}>} TOTAL_EXAM))

I need sum the total exams from the second exam for distinct users. How can i do it?

1 Solution

Accepted Solutions
Partner - Master III

hi

in this case you need to use the aggr function so you can evaluate the expression for each student in each year

sum(aggr(IF(SUM({\$<USER = {"NORMAL"}, COD_EXAM ={"40302733","40302075"},AGE={">=19"}*{"<=75"}>} TOTAL_EXAM) > 1 ,
SUM({\$<USER = {"NORMAL"}, COD_EXAM = {"40302733","40302075"},AGE={">=19"}*{"<=75"}>} TOTAL_EXAM)),USER_ID,YEAR))

you need to put the actual field names of user id and year in the end of the expression above for it work

3 Replies
Partner - Master III

hi

in this case you need to use the aggr function so you can evaluate the expression for each student in each year

sum(aggr(IF(SUM({\$<USER = {"NORMAL"}, COD_EXAM ={"40302733","40302075"},AGE={">=19"}*{"<=75"}>} TOTAL_EXAM) > 1 ,
SUM({\$<USER = {"NORMAL"}, COD_EXAM = {"40302733","40302075"},AGE={">=19"}*{"<=75"}>} TOTAL_EXAM)),USER_ID,YEAR))

you need to put the actual field names of user id and year in the end of the expression above for it work

Contributor II
Author

Worked. Thank u so much 🙂

Partner - Master III

hi

great , i am always happy to help

Community Browser