Select the active record

[xarapre7]

Hello!

I have data that shows employee_key, employee_name, employee_id, emp_status and country.
An employee can have multiple emp_status.  My expression in the text box is below:

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/
Count({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} Aggr(If(Sum({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

Right now, it's picking whatever is the 1st record for that employee which doesn't tie up to any country so the output it gives is 0.

EMP_KEY, EMP_NAME, EMP_ID, EMP_STATUS, COUNTRY
123             JOE               12001      WITHDRAWN     -
123             JOE               12001      WITHDRAWN     -
234             JOE               12010      ACTIVE              US

What's the right expression to only display country from the active record?

Thanks in advance for your help!

[Gysbert_Wassenaar]

What's the right expression to only display country from the active record?

Only({<EMP_STATUS={'ACTIVE'}>}COUNTRY)

[Anil_Babu_Samineni]

You written this from Text box, Do you mean text object?

Text object won't get the string by using Set analysis. Can you describe more with expected output

Try Something like this, I mean merge this to show only Active Data point

EMP_STATUS={'ACTIVE'}

[xarapre7]

Thanks for the reply.  I tried something like below and it's still giving me 0.

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/

Count({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} Aggr(If(Sum({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} Only({<EMP_STATUS={'ACTIVE'}>} COUNTRY), CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

Did I put it in the right location?

[Miguel_Angel_Baeyens]

Set analysis works anywhere in QlikView where an expression is accepted. Text objects do work with set analysis, if the expression is correct.

[Miguel_Angel_Baeyens]

Have not tested, but should be something like:

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/Count({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY), EMP_STATUS={'ACTIVE'}>} Aggr(If(Sum({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

[sunny_talwar]

Not entirely sure what you goal is, but may be this:

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/

Count({$<EMPLOYEE_NAME =,COUNTRY = p(COUNTRY)>} Aggr(If(Sum({$<EMPLOYEE_NAME =,COUNTRY = p(COUNTRY), EMP_STATUS={'ACTIVE'}>} CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

[sunny_talwar]

mbaeyens‌

This doesn't seem right

[Miguel_Angel_Baeyens]

Yep, as I said, I have not tested it, just copypasted the expression from the post above. Anyway, I have fixed it now (still untested)

[Anil_Babu_Samineni]

Agree 100%, By using set operator how do we get String. Can we have any hierarchy to get the string or atleast agenda.