How do I write an expression to COUNT a Variable?

[#1]

I created a variable to tell me whether a Student has passed a Class. It gives me either a "Y" or a "N".

I wrote the expression as - Count($(vLevel0Completion)="Y"),[SID])

(vLevel0Completion) is my variable the tells me Y or N

[SID] is what I am trying to count (STUDENT) if they have a "Y" status within my variable.

I am trying to put this expression into the KPI object. I'm currently getting a line and I am unable to figure out why I can't count. Let me know. All help is appreciated.

[shree909]

Try this

If($(vLevel0Completion)="Y", count([SID]))

[sunny_talwar]

Do you have a variable for each student? Or is vLevel0Completion a field? Not sure I understand your data, would you be able to provide more details here?

[#4]

See below - I created a variable for each CLASS that makes up Level 0 Completion. Long story short - they have to get a "Y" in each class to get the "Y" status in Level 0 Completion.

My vLevel0Completion expression -

If (not match ($(vLevel0Course1),'N') and not match ($(vLevel0Course2),'N') and not match ($(vLevel0Course3),'N') and not match ($(vLevel0Course4),'N') and not match ($(vLevel0Course5),'N') and not match ($(vLevel0Course6),'N') and not match ($(vLevel0Course7),'N') and not match ($(vLevel0Course8),'N') and not match ($(vLevel0Course9),'N') and not match ($(vLevel0Course10),'N') and not match ($(vLevel0Course11),'N'),'Y','N')

[#5]

That didn't work. Thanks for trying though! I appreciate it!

[sunny_talwar · Accepted solution]

Won't be 100% sure, but how about this in KPI Object:

Sum(Aggr(If($(vLevel0Completion) = 'Y', 1, 0),[SID]))

[#7]

That gave me a number.

My total student count in 24,238.

Your expression gave me 20,266 that passed level 0

The number I'm trying to match is 19,711 for level 0.

The numbers are close. Any idea on why they might not match? Is there a distinct feature we could add?

[sunny_talwar]

This is already distinct for each SID. Would you be able to share a sample to take a look at?

[#9]

I don't think I can share that much (work stuff and includes confidential identities). I like my job and want to keep it. Haha.

So my data source is currently an excel file (temporary). We will be connecting to data table eventually. I'm doing a jump start to try to recreate the logic we used in our old reporting system into this new data analytic tool (Qlik). So I have the numbers in the excel I am trying to match. This might be as far as you can take me. I did a data export though with the KPI expression you provided and it did count the same number as the KPI. I think I will just have to dig further to identify the disconnect.  I appreciate your help and providing me a solution to my problem!