Count in Expression

[#1]

Hi,

please help to solve the issue:

I need to count students with school = home_school only.

Statement : =COUNT({<SCHOOL = HOME_SCHOOL>} DISTINCT [STUDENT NUMBER])  

does not give any results.

If I hard code it works 

Thanks,

L

[krishna_2644]

This should do, in your scenario:

=count({$<HOME_SCHOOL = p(SCHOOL)>} [Student Number])

[sunny_talwar]

Check this out. Added a new row of data

Table:

LOAD * Inline [

Student Number, HOME_SCHOOL, SCHOOL

10417, 2522, 2522

78894, 2856, 2522

91282, 2522, 2522

23133, 2522, 2522

78256, 2522, 2522

46204, 2522, 2522

55106, 2522, 2522

26372, 2522, 2856

];

The output should still remain 6, I believe

[sunny_talwar]

Apologize

You can learn some set analysis from the attached. But more advance stuff can be learned as you play around with it and try to help others on the community.

Best,

Sunny

[#5]

the business rule might help:

Every student can have only one HOME_SCHOOL but many SCHOOLs (enrolment school)

Every SCHOOL counts students only if HOME_SCHOOL = SCHOOL

[sunny_talwar]

If a student can have a multiple SCHOOLs, you need to create a new key, Something like this:

AutoNumber([Student Number]&HOME_SCHOOL&SCHOOL) as Key

and use this as your expression:

=COUNT({<Key= {"=SCHOOL = HOME_SCHOOL"}>} DISTINCT [Student Number])

[#7]

Thank you Sunny for your time!

L

[sunny_talwar]

Did it final work out for you?

Also, I would ask you to mark any helpful answers.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[#9]

It did work out for me.

Thanks for the Marking Replies reminder.

[sunny_talwar]

I am glad it finally did

Best,

Sunny