Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner
Partner

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
Partner
Partner

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

Contributor II
Contributor II

Worked. Thank u so much 🙂

Partner
Partner

hi 

great , i am always happy to help