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: 
ssssssss88888888
Contributor II
Contributor II

How to Count the Amount of Instances Where Employees Have Satisfied a Condition

Hello, I'm new here, I don't a data set to share but will do my best to explain. We have a certain metric here where people have to complete a project at least 4 times per year. Thus, I would like to basically assign a value of 1 (or 4) if all four projects have been completed. I would like to assign a value of 0 if all 4 projects have not been completed. In other words, if you did 4, 5, 10, or 100 projects, it doesn't matter; you have satisifed the criteria and should be assigned a value of 1. If you completed 0,1, 2 or 3 projects, it also doesn't matter; you have not satisfied the criteria yet and should be assigned a value of 0. Now, I have a large set of records of which it is easily to tabulate total overall sum and other figures. What I would like to do is get a count from those records of how many employees have satisfied the criteria. For example, say we have 300 employees. If 200 of them have completed 4 or more projects, then we would be 67 percent complete overall, even though the total number of projects would hundreds or thousands higher. Hope this makes sense and please let me know if you have any questions!! PS would like to do this as a chart function, if possible!

Labels (2)
3 Replies
edwin
Master II
Master II

as it sounds like the situation will not change once the load is done, you should make this determination in the load script.  i f your data consists of some people dientification say person_ID, and a project identifier project_ID, you can aggregate it:

AGGR:
load person_ID, if(count(project_id)>=4,1,0) as someField resident YOUR_FACT group by person_ID;

inner join (YOUR_FACT) load person_ID, someField resident AGGR;

drop table AGGR;

ssssssss88888888
Contributor II
Contributor II
Author

thank you so much Edwin, you are always so helpful! Just out of curiousity, is this possible to do at the level of the chart function? We are working in the Dev environment, so a bit difficult to use the data load editor for this current issue. But if that's really the only way, then I really appreciate it!!!

edwin
Master II
Master II

pls post sample data it does not have to be the full data and real data.  just enough to be able to model your scenario