Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
maverjannetbunnyqlik MarcoWedel
this is live example for count pass/fail students..
i need to count the failed students in pivot table like the example
regards.
Hi,
Can you please explain what you want exactly in pivot . Which count ?
Thanks,
Bunny
use this Expression in pivot table
count({<Result_field={"fail"}>}Result_field )
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.
!
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
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.
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
try
Count(if(PASS_FAIL ='F',PASS_FAIL ,0)) as Sub_Failed
try
count(DISTINCT if(PASS_FAIL = 'F', People_ID)) or
count(DISTINCT{<PASS_FAIL = {'F'}>} People_ID)
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