count pass/fail students in pivot table

[Anonymous]

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.

[Anonymous]

Hi,

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

Thanks,

Bunny

[ahmar811]

use this Expression in pivot table

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

[Anonymous]

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.

[#5]

!

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

[Anonymous]

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.

[Anonymous]

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

[#8]

try

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

[#9]

try

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

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

[Anonymous]

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