Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mikeg_33
New Contributor III

set analysis for KPI

Greetings Community,

I am attempting to create a KPI to show me the people that have fully completed there training model.

based on what I have:

Count({<transcript_status_code = {'A'},training_model_date -= null>}aggr(count(transcript_status_code),employee_id,course_code))

/

Count({< training_model_date -= null, course_code -= null>}aggr(count(Employee),Employee,training_model_date,course_code))

I need the KPI to count the Employees that have a 1 in the measure column within the Image I have posted.

any ideas are appreciated!

1 Solution

Accepted Solutions
MVP
MVP

Re: set analysis for KPI

May be try this

Count(DISTINCT {<Employee = {"=Count({<transcript_status_code = {'A'},training_model_date -= null>} Aggr(Count(transcript_status_code),employee_id,course_code)) / Count({< training_model_date -= null, course_code -= null>} Aggr(count(Employee),Employee,training_model_date,course_code)) = 1"}>} Employee)

4 Replies
MVP
MVP

Re: set analysis for KPI

May be try this

Count(DISTINCT {<Employee = {"=Count({<transcript_status_code = {'A'},training_model_date -= null>} Aggr(Count(transcript_status_code),employee_id,course_code)) / Count({< training_model_date -= null, course_code -= null>} Aggr(count(Employee),Employee,training_model_date,course_code)) = 1"}>} Employee)

mikeg_33
New Contributor III

Re: set analysis for KPI

This is close!

I did another measure next to it:

if(Count({<transcript_status_code = {'A'},training_model_date -= null>}aggr(count(transcript_status_code),employee_id,course_code))

/

Count({< training_model_date -= null, course_code -= null>}aggr(count(Employee),Employee,training_model_date,course_code))>= 1,1,0)

and changed the "totals column" to SUM and it retrieved 17 (which is the number im looking for)

mikeg_33
New Contributor III

Re: set analysis for KPI

Count(DISTINCT {<Employee = {"=(Count({<transcript_status_code = {'A'},training_model_date -= null>} Aggr(Count(transcript_status_code),employee_id,course_code)) / Count({< training_model_date -= null, course_code -= null>} Aggr(count(Employee),Employee,training_model_date,course_code)))=1"}>} Employee)

I added the changes in red and got my answer!

MVP
MVP

Re: set analysis for KPI

Yes you are right... I was getting out of work so typed that response in a rush .

Modifying my response for future visitors.