Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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])

 

Highlighted
Contributor III
Contributor III

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 

Highlighted

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])
Highlighted
Contributor III
Contributor III

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

 

Highlighted

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.

Highlighted
Contributor III
Contributor III

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.

 
 

 

 
 

 

Highlighted

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