If formula

Question

Hello - Taking the Impact column as a base in consideration with the Account/Department/Intercompany i need to derive the Journal type column.

For example: Journal entry 1, 4, 5 are B/S Only  but having different scenarios.

* Journal entry 1 is B/S only with different accounts and different departments - Then Journal type is B/S reclass or GND reclass
* Journal entry 4 is B/S only with same accounts, no intercompany but different departments - Then Journal type is DEPT transfer
* Journal entry 5 is B/S only with same accounts, same departments but one line is having intercompany and other line does not - Then journal type is Intercompany transfer

Except the Journal 5 scenario the intercompany code may exist or may not exist in the other journal entries

This is how i need to define the Journal type. Please help

Journal EntryAccountDepartmentIntercompanyNatureImpactJournal type110000012345DEFAssetB/S OnlyB/S reclass or GND reclass120000034567 LiabilityB/S OnlyB/S reclass or GND reclass       240000089111 RevenueIS OnlyI/S reclass or GND reclass250000012134 ExpenseIS OnlyI/S reclass or GND reclass270000015167 ExpenseIS OnlyI/S reclass or GND reclass       310000018192GHIAssetB/s and ISMAN PL ADJ340000021223 RevenueB/s and ISMAN PL ADJ       410000099999 AssetB/S OnlyDEPT Transfer410000088888 AssetB/S OnlyDEPT Transfer       510000012345ABCAssetB/S OnlyIntercompany transfer510000012345 LiabilityB/S OnlyIntercompany transfer

Yoshiro_Fujimori · Answer

Hi @ashaik1 ,

I tried to follow your expected outcome. I hope this may help.

Worklfow

Formula tool 1

sameAccount = [CountDistinct_Account]=1

sameDepartment = [CountDistinct_Department]=1

Formula tool 2

Journal type =

IF ![sameAccount] AND ![sameDepartment] AND [Impact] = "B/S Only"
THEN "B/S reclass or GND reclass"
ELSEIF ![sameAccount] AND ![sameDepartment] AND [Impact] = "IS Only"
THEN "I/S reclass or GND reclass"
ELSEIF [Impact] = "B/s and IS"
THEN "MAN PL ADJ"
ELSEIF [sameAccount] AND ![sameDepartment] AND [CountNonNull_Intercompany] = 0
THEN "DEPT Transfer"
ELSEIF [sameAccount] AND [sameDepartment] AND [CountNonNull_Intercompany] = 1
THEN "Intercompany transfer"
ELSE Null()
ENDIF

Output

1247223.yxmd