What's wrong with this metric?

Question

I have this metric:

SUM(

CASE

WHEN "HCM - Workforce Core"."HR Action"."Action Code" in ('HIRE','ADD_CWK','REN_CWK','ADD_CWK_WORK_RELATION') AND "HCM - Workforce Core"."Assignment Information"."Primary Flag Code" = 'Y' AND "HCM - Workforce Core"."Assignment Information"."Assignment Status Type Code" = 'ACTIVE'

THEN 1

ELSE 0

END

)

What could be the reason why from time to time it counts 2 on the same person?

For instance person 2333 has only 1 assignment so I expect to count 1. He was also terminated once but the "Assignment Status Type Code" = 'ACTIVE' filter should exclude the record. What else could be the cause?

Jestin Rajan · Answer

https://community.oracle.com/products/oracleanalytics/discussion/comment/71023#Comment_71023

Thanks for the details. The formula is correct, but seeing 2 for person 2333 likely means there are duplicate rows in the source table. This can happen if another column differs, even with the same person and assignment. I believe rewriting the formula to handle duplicates would be the right approach.

Stefano_Mazzocca · Answer

Hi @JohnW-Oracle , you probably missed the last rows of the message:

For instance person 2333 has only 1 assignment so I expect to count 1. He was also terminated once but the "Assignment Status Type Code" = 'ACTIVE' filter should exclude the record. What else could be the cause?

Do you have any clue?

Thank you very much

JohnW-Oracle · Answer

Hello @Stefano_Mazzocca

I don't think we have enough details to provide a specific answer but the starting point would be FA>HCM and check the history of PersonID 2333. Does the person have more than one assignment, are they all inactive, when and how was the change made compared to the last Pipeline?

Maybe if you added the logical SQL (with no PI) we could add more.

Regards,

John