Set analysis doesn't work

[#1]

Hello everyone,

I try to calculate how many employee worked within a month.

I use this scrip to calculate the total employee within a month:

count(distinct if( CalendarMonthEnd>=HireDate and CalendarMonthStart <=if( EmplStatus='ACT',date(today()*2),MonthEnd(TERM_DT)),EMPL_ID))

This script works good.

Then I need analysis termination reason. I add Voluntary_Ind to this script. I changed the script to:

count({$<Voluntary_IND=,  HireDate = {"<=CalendarMonthEnd"}, TERM_DT={">=CalendarMonthStart"}>} distinct EMPL_ID)+ count(distinct if(CalendarMonthEnd>=HireDate and EmplStatus='ACT',EMPL_ID))

First part of this scrip is return 0.

Can you help me find a solution on this issue?

Thanks,

Dust

[jagan]

Hi,

Try this

count({$<Voluntary_IND=,  HireDate = {"<=$(=CalendarMonthEnd)"}, TERM_DT={">=$(=CalendarMonthStart)"}>} distinct EMPL_ID)

If this expression not works then check the date format of HireDate and CalendarMonthEnd should be same and

TERM_DT and CalendarMonthStart format should be same.

In expression "Voluntary_IND=," this will ignore the selections in the Voluntary_IND= field.

Regards,

Jagan.

[#3]

Thank you -- Jagan.

Please see my testing file. It still cannot get number.

I appreciate your answer.

Can you take a look?

[#4]

We are getting closer. Can any guru take a look my file and have a solution?

Thank you very much!