topic Re: Counts Expression in QlikView

Counts Expression

Fri, 27 Nov 2015 03:33:12 GMT

Hi Everyone,

I have a scenario where i need to show the records which are Student Status = Pass and i should exclude Phase = Absent. My Max Reporting date is November 11, 2015. Now i want the students list who are pass before November 11, 2015.

Count({<[Student Status]={'Pass'},StartDate={'<$(=vMaxReportDate)'}, [Phase]-={'Absent'}> +

<[Student Status]={'Pass'},[Phase]-={'Absent'}, EndDate={'<$(=vMaxReportDate)'}>} Distinct [Student ID])

Thank You

Re: Counts Expression

tamilarasu — Fri, 27 Nov 2015 13:19:34 GMT

Hi Jack,

Not sure what is your start date and end date. I assume you want the count of student ID's whose phase is not absent and enf date which is less than Report date. Check the below one.

Count({<[Student Status]={'Pass'},EndDate={'<$(=vMaxReportDate)'}, [Phase]-={'Absent''}>} Distinct [Student ID])

Also note your End Date field format should match with vMaxReportDate variable format.

Re: Counts Expression

Anonymous — Fri, 27 Nov 2015 13:35:48 GMT

like this:

Let VPassDate=Date('November 11, 2015','DD/MMM/YYYY');

make sure PassedDateField should be in 'DD/MMM/YYYY' format..

=count({<StudentStatus={'Pass'}, Phase -={'Absent'}, PassedDateField={'<=$(=VPassDate)'} >} distinct StudentID)

Hope this will help!!

Re: Counts Expression

Sun, 29 Nov 2015 10:57:25 GMT

Both my start and end date is less than November 11, 2015

Re: Counts Expression

tamilarasu — Mon, 30 Nov 2015 01:39:17 GMT

You can try,

Count({<[Student Status]={'Pass'},StartDate={'<$(=vMaxReportDate)'},EndDate={'<$(=vMaxReportDate)'}, [Phase]-={'Absent'}>} Distinct [Student ID])

As already mentioned, your vMaxReportDate variable format should match with StartDate and end date fields.

Re: Counts Expression

Mon, 30 Nov 2015 02:23:46 GMT

try this

count({<StudentStatus={'Pass'}, Phase -={'Absent'}, Date={'<=$(=max(Date)'} >} distinct ID)