Sum values greater than 1 of distinct users

[eloisateixeira]

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?

[lironbaram · Accepted solution]

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]

Worked. Thank u so much 🙂

[lironbaram]

hi 

great , i am always happy to help