Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
georgpa22
Contributor
Contributor

Conditional Statements Help

Hi,

I am new to Qlik scripting and I am having some difficulty with some basic things. I have a simple dataset containing the columns "Status", "Score" and "Reason", with entries in each of these columns being {Rejected, Hired}, [0,100], {Hired, Communication Skills, Aptitude}, respectively. 

There are approximately 1000 datapoints corresponding to people and I want to start off by doing some simple things, such as counting just how many people were hired. To do this, I tried something like:

Count([Status] = "Hire") which didn't work.

This ended up working, but I don't know why: 

=Count({<[Status]={"Hire"}>}Status) 

Could somebody please explain why this is the case?

Then I wanted to check how many people got hired that also  achieved a better score than the median (AND).

For this I tried:

=Count({<[Status]={"Hire"}, [Score]>= median(Score)>}Status) 

which didn't work. Could someone help out with this?

Then I wanted to make a pie chart indicating the reason why candidates were not hired, when they achieved a lower score than the median. For this I tried using the following statement as the expression in the pie chart:

if([Status] = 'Rejected' and [Score] < median(Score), Count([Reason])) 

while "Reason" was used as the dimension.

Thank you for any help!

Labels (3)
8 Replies
JustinDallas
Specialist III
Specialist III

You have a lot of questions and it's hard to discern what should and shouldn't be answered.  For your first question, 

This won't work: Count([Status] = "Hire")

and this will: Count({<[Status]={"Hire"}>}Status)

 

because the latter is what we call set analysis.  You will be forced to become familiar with it as you delve further into the Qlik world.

dreweezy
Partner - Creator II
Partner - Creator II

Then I wanted to check how many people got hired that also  achieved a better score than the median (AND).

For this I tried:

=Count({<[Status]={"Hire"}, [Score]>= median(Score)>}Status) 

which didn't work. Could someone help out with this?

 

Can you try - 

=Count({<[Status]={"Hire"}, [Score] > median(Score)>} [Status]) 

georgpa22
Contributor
Contributor
Author

Thanks for the reply!

This didn't work either unfortunately.

georgpa22
Contributor
Contributor
Author

Sure, that part is fine. But when I want to add another condition involving an inequality (greater/less than) how do I do this?

In fact, this works fine:

=Count({<[Status]={"Hire"}, [Score] = {99}>} [Status])

However, how could I adjust this so that it uses a "greater than" rather than "=", because this does not work:

=Count({<[Status]={"Hire"}, [Score] >= {99}>} [Status])

Moreover, if I try to use the median of the Score (shown below), this also does not work. Could you please explain why?

=Count({<[Status]={"Hire"}, [Score] = {median(Score)}>} [Status])

georgpa22
Contributor
Contributor
Author

Sure, that part is fine. But when I want to add another condition involving an inequality (greater/less than) how do I do this?
In fact, this works fine:
=Count({<[Status]={"Hire"}, [Score] = {99}>} [Status])
However, how could I adjust this so that it uses a "greater than" rather than "=", because this does not work:
=Count({<[Status]={"Hire"}, [Score] >= {99}>} [Status])
Moreover, if I try to use the median of the Score (shown below), this also does not work. Could you please explain why?
=Count({<[Status]={"Hire"}, [Score] = {median(Score)}>} [Status])
dreweezy
Partner - Creator II
Partner - Creator II

Can you try this?

=Count({<[Status]={"Hire"}, [Score] {'>= 99'}>} [Status])

For this issue:

=Count({<[Status]={"Hire"}, [Score] = {median(Score)}>} [Status])

can you try 

=Count({<[Status]={"Hire"}, [Score] = {"=median(Score)"}>} [Status])

 

CELAMBARASAN
Partner - Champion
Partner - Champion

You should write the expression which has to evaluated dynamically in a double quotes and also need to do it overall then add $() as well

 

=Count({<[Status]={"Hire"}, [Score] = {">$(=median(Score))"}>} [Status])

georgpa22
Contributor
Contributor
Author

Awesome, this works! Why is the dollar sign expansion needed though?