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!

[Miguel_Angel_Baeyens]

Then you have to find out if there is a data model or data issue causing the unexpected results. Like to lines for the same incident ACTIVE.

Anyway, the way I usually deal with these complex expressions is using a straight table with several columns, instead of one single try to a very big expression. Like see if you get the right number of calls without any filters, then only active, then the number of employees overall, etc.

[martynlloyd]

You could save yourself a lot of syntax heartache by flagging active employees in the table load

Load Distinct

EMP_KEY,

IF(EMP_STATUS = 'ACTIVE', 1, 0) as ACTIVE_EMP_COUNT

...

;

You can then use simple expressions like Sum({$<EMPLOYEE_NAME= >} ACTIVE_EMP_COUNT)

Regards,

Marty.