Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
eloisateixeira
Contributor II
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
lironbaram
Partner - Master III
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 

View solution in original post

3 Replies
lironbaram
Partner - Master III
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 

eloisateixeira
Contributor II
Contributor II
Author

Worked. Thank u so much 🙂

lironbaram
Partner - Master III
Partner - Master III

hi 

great , i am always happy to help