logic help?

[Navya08]

I have this excel report i need to meet below criteria :

not sure how can we achieve this?

if employee is located in us/uk: transaction date should not be after oct 7 2024

If employee is remote worker as stated in location name column transaction date should not be after oct 9 2024

If employees located in brazil cyprus szech republic, france, Germany, japan korea, uae ,sweden then transaction date should not be after oct 22 2024

IF employees is from Australia hong kong, Singapore then transaction date should not be after 28 oct 2024

New hire onboarded between June 20 2024-oct 24 2024 which can be seen in hire date column then transaction date should not be after noy 4 2024

if employees located in canada, Italy, india then transaction date should not be after 8nov 2024

If employees of cost rica: transaction date should not be after 2nov 2024

New hire between oct 23 2024-nov, 13 2024 -transaction date should not be after 2 nev 2024

something apart from this then transaction date should not be after transcript completed date.

country codes: 

IND INDIA

BEL BELGIUM

AUT AUSTRIA

AUS AUSTRALIA

SGP SINGAPORE

FRA FRANCE

CRI  COSTA RICA

GBR UNITED KINGDOM

US.  UNITED STATES OF AMERICA

HKG HONG KONG

BRA. BRAZIL

SAU SAUDI ARABIA

CZE CZEH REPUBLIC

SWE SWEDEN

ITA ITALY

CAN. CANADA

JPN JAPAN

ARG argentina

ARE united arab emirates

LTU LITHUANIA

DEU GERMANY

if anyone can help me with sample workflow

[Deano478]

@Navya08 Can you share what you've tried so far so everyone has an idea as it seems you haven't attempted this yet yourself?

[Navya08]

I am not sure how to build the logic for country and date together so need help

[Navya08]

Not sure how to compare date and country logic together , can someone help me with formula tool - and the logic that will be made

[apathetichell]

Make a new table of countries and dates. join new table to your original table on country field. compare dates. do your logic. Note ---- for unmatched countries you should use a union tool to join back in the records which do not map.

Are country names not standardized/the same? fix them. That is part of the problem. Use a formula tool.

[Hsandness]

Hi @Navya08 ,

I've helped create the first two filters for you. You'll want to convert the Transaction Completed Date column to a datetime field for easier filtering. I wasn't sure how you want to handle employees that have transaction dates greater than the dates you've specified. You'll have to determine that after the True and False output anchors.

For each logical filter, you can add additional filters or have all of them in one filter. Example, you could look for Brazil, Czech, Japan, France, Germany, Korea, UAE, Sweden, etc. in one filter using the Location or Country column and then you just have to check the transaction date after that. Or you can use a static file to match the country codes and do the filter after that if you need to bring in the country names themselves.

I've left the remaining filters for you to decide if you want to use this approach or do something different.

[PangHC]

@Navya08 just do it by grouping the criteria.
for example, check the country > remote > new hire > transcript completed date