Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gilldilpreet
Contributor III
Contributor III

Write set analysis expressions

Hi 

Please see attached the snapshot of what I am trying to achieve. 

Ideally, I would've liked to create the blue text boxes as a table rather than individual boxes but will manage with this. 

Could someone please help me to write the expressions for boxes labelled '?' - the values populated in the table below are count of Student IDs (i.e. number of students). 

Thanks in advance for your help!

Dilpreet 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

Try this

 =Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Student ID]) < 5"}>} [Module Title])

Or may be you want to do a DISTINCT count of Student ID

 =Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count(DISTINCT {<[Effective Term] = {'201617'}>} [Student ID]) < 5"}>} [Module Title])

 

View solution in original post

7 Replies
sunny_talwar

Try these

Count({<[Effective Term] = {"201617"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {"201617"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 10"}>} [Module Title])

Count({<[Effective Term] = {"201718"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {"201718"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 10"}>} [Module Title])

Count({<[Effective Term] = {"201819"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {"201819"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 10"}>} [Module Title])

 

gilldilpreet
Contributor III
Contributor III
Author

Hi, 

I am getting the attached error. 

Also, the count I need to be less than 5 or 10 is the count of Student IDs (number of students). 

So, I am trying to get the count of modules in each year that have a count of less than 5 students or 10 students.

Hope that makes sense, thanks for your help so far! 

Dilpreet 

sunny_talwar

May bad, try these instead

Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Module Title]) < 10"}>} [Module Title])

Count({<[Effective Term] = {'201718'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {'201718'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Module Title]) < 10"}>} [Module Title])

Count({<[Effective Term] = {"201819"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {"201819"}, [Module Title] = {"=Count({<[Effective Term] = {"201617"}>} [Module Title]) < 10"}>} [Module Title])
gilldilpreet
Contributor III
Contributor III
Author

Hi, 

These don't seem to be working either 😞 

They are also missing the 'Student ID' component - I need to count the number of modules with less than 5 / less than 10 students across the 3 years. These are the figures I'm getting with the expressions suggested above but they are not correct...?

gilldilpreet_0-1596536766215.png

 

sunny_talwar

Not sure if my typo caused an issue, but can you try this

Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Module Title]) < 10"}>} [Module Title])

Count({<[Effective Term] = {'201718'}, [Module Title] = {"=Count({<[Effective Term] = {'201718'}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {'201718'}, [Module Title] = {"=Count({<[Effective Term] = {'201718'}>} [Module Title]) < 10"}>} [Module Title])

Count({<[Effective Term] = {"201819"}, [Module Title] = {"=Count({<[Effective Term] = {"201819"}>} [Module Title]) < 5"}>} [Module Title])

Count({<[Effective Term] = {"201819"}, [Module Title] = {"=Count({<[Effective Term] = {"201819"}>} [Module Title]) < 10"}>} [Module Title])

I acctidentally used 201617 for all the count conditions previously.

gilldilpreet
Contributor III
Contributor III
Author

The expressions are working (i.e. not returning an error) but its not giving me the value I need.

I'm assuming this is because the expression is not factoring in the 'Student ID' field. 

The attached seems closer to what I am trying to do but I'm not sure why I'm getting the error at the end.

 
 

 

 
 

 

sunny_talwar

Try this

 =Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count({<[Effective Term] = {'201617'}>} [Student ID]) < 5"}>} [Module Title])

Or may be you want to do a DISTINCT count of Student ID

 =Count({<[Effective Term] = {'201617'}, [Module Title] = {"=Count(DISTINCT {<[Effective Term] = {'201617'}>} [Student ID]) < 5"}>} [Module Title])