topic Re: Write set analysis expressions in QlikView

Write set analysis expressions

gilldilpreet — Sat, 16 Nov 2024 00:13:07 GMT

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

Re: Write set analysis expressions

sunny_talwar — Thu, 30 Jul 2020 19:56:09 GMT

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

Re: Write set analysis expressions

gilldilpreet — Fri, 31 Jul 2020 10:12:34 GMT

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

Re: Write set analysis expressions

sunny_talwar — Fri, 31 Jul 2020 11:32:29 GMT

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

Re: Write set analysis expressions

gilldilpreet — Tue, 04 Aug 2020 10:27:44 GMT

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...?

Re: Write set analysis expressions

sunny_talwar — Tue, 04 Aug 2020 10:58:47 GMT

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.

Re: Write set analysis expressions

gilldilpreet — Wed, 05 Aug 2020 10:04:26 GMT

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.

Re: Write set analysis expressions

sunny_talwar — Wed, 05 Aug 2020 10:57:18 GMT

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