topic Re: count pass/fail students in pivot table in QlikView

count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 05:55:39 GMT

maverjannet‌bunnyqlik‌ MarcoWedel‌

this is live example for count pass/fail students..

i need to count the failed students in pivot table like the example

regards.

Re: count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 06:05:01 GMT

Hi,

Can you please explain what you want exactly in pivot . Which count ?

Thanks,

Bunny

Re: count pass/fail students in pivot table

ahmar811 — Mon, 19 Oct 2015 06:09:23 GMT

use this Expression in pivot table

count({<Result_field={"fail"}>}Result_field )

Re: count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 07:00:44 GMT

Dear Ahmar,

i think it's wrong to use only pass_fail field = fail ?! because i need to count the students which failed in 1 unit only, and 2 units, and 3 units.. so i need something like this:

count(aggr(-(count({<[Pass_Fail]={'f'}>} People_ID) =2), People_ID))

but this expression doesn't give me actual result !

Regards.

Re: count pass/fail students in pivot table

Mon, 19 Oct 2015 07:39:36 GMT

Hi,

Please see file attached for a solution i have altered your formula to

Failed 1 Unit = sum(if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=1,1,0))

Failed 2 Unit = sum(if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=2,1,0))

Failed 3 Unit = sum(if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=3,1,0)) .... etc .

I would recomend that you change your script to add a new field that does the count on the back end.

Use Script Below:

RawLoad:

SQL SELECT "DEGREE" as "Degree",

"EVENT_LONG_NAME" as "Event",

"ACADEMIC_TERM" as "Academic_Term",

"ACADEMIC_YEAR" as "Academic_Year",

"CURRICULUM" as "Curriculum",

"CLASS_LEVEL" as "Class_Level",

"PEOPLE_ID" as "People_ID",

"FINAL_GRADE" as "Grade"

FROM "PCDB_PROD20150906".dbo."VW_TotalRegistration";

SQL SELECT "ACADEMIC_TERM",

"ACADEMIC_YEAR",

"EVENT_ID",

"FINAL_GRADE",

"PASS_FAIL",

"PEOPLE_ID" as "People_ID"

FROM "PCDB_PROD20150906".dbo."VW_Pass/Fail";

Left Join

Load

People_ID,

Count(if("PASS_FAIL"= 'F', 1, 0)) as Sub_Failed

Resident RawLoad

Group By People_ID;

Regards

Rahul

Re: count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 09:48:22 GMT

Thanks a lot for help

what about this expression to count "Failed at Least One Unit" >>

count(DISTINCT{<Pass_Fail = {'F'}>} People_ID)

can you please send me the right expression because this one is wrong

Regard.

Re: count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 09:55:57 GMT

and for the first query you give it to me, when i reload the data in qlikview this error is appear:

Script Error:

Field not fount - <PASS_FAIL>

left Join

Load

People_IDm

Count(if("PASS_FAIL" ='F',1,0)) as Sub_Failed

Resident RawLoad

Group By People_ID

Re: count pass/fail students in pivot table

Mon, 19 Oct 2015 10:06:43 GMT

try

Count(if(PASS_FAIL ='F',PASS_FAIL ,0)) as Sub_Failed

Re: count pass/fail students in pivot table

Mon, 19 Oct 2015 10:07:30 GMT

try

count(DISTINCT if(PASS_FAIL = 'F', People_ID)) or

count(DISTINCT{<PASS_FAIL = {'F'}>} People_ID)

Re: count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 10:18:39 GMT

Hi,

Try this,

sum(if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=1,1,0))+

sum(if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=2,1,0))+

sum(if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=3,1,0))+

sum(if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=4,1,0))

Hope including all failed is the desired value .

Thanks,

Bunny

Re: count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 10:21:41 GMT

It's work thanks

what about [ Passed all unit] i try to use this two expression but it's all wrong >>

=if(Pass_Fail <> 'F',count( Pass_Fail))

=count(DISTINCT if(PASS_FAIL <> 'F', People_ID))

Regard.

Re: count pass/fail students in pivot table

rubenmarin — Mon, 19 Oct 2015 10:34:03 GMT

Hi Mohammed, maybe with:

count(DISTINCT People_ID)-count(DISTINCT{<PASS_FAIL = {'F'}>} People_ID)

Or:

count(DISTINCT People_ID)-count(DISTINCT if(PASS_FAIL <> 'F', People_ID))

If it can be people_Id without evalutions you should filter those:

count(DISTINCT{<PASS_FAIL = {'F','P'}>} People_ID)-count(DISTINCT{<PASS_FAIL = {'F'}>} People_ID)

Re: count pass/fail students in pivot table

Mon, 19 Oct 2015 10:39:46 GMT

if i was you i would use:

Count(distinct if((Aggr(sum(if(PASS_FAIL = 'F',1,0)), People_ID))=0,People_ID,0))

The reason being, we want to calculate this over People_ID. Hope that makes sense.

Regards

Re: count pass/fail students in pivot table

Anonymous — Mon, 19 Oct 2015 10:42:34 GMT

Hi,

There at total it should be passed + Failed.

Because there are some records which are not either comes under Passed or Failed.

Thanks,

Bunny