I am creating a G/L Reclass entry for our trial balance. I have all the values I need in a field titled difference, but I need a series of rows (G/L accounts) to be the negative of my difference column. G/LG/L BalanceExpected ValueDifferenceReclass 6000$100$0$100-$100This reclass for 6000 is what I am trying to recreate. It should be the inverse of the difference column.5000$0$100$100$100 At Present I'm attempting to do this: IF [G/L AC]=505000 THEN 0 ELSEIF [G/L AC]=599999 THEN 0 ELSEIF [G/L AC]=550100 THEN 0 ELSEIF [G/L AC]=822222 THEN 0 ELSEIF [G/L AC]>=899220 THEN 0 ELSEIF [G/L AC]>=556000 THEN -[Difference] ELSEIF [G/L AC]<558299 THEN -[Difference] ELSE [Difference] ENDIF

Create an negative value (inverse) of another field

I am creating a G/L Reclass entry for our trial balance. I have all the values I need in a field titled difference, but I need a series of rows (G/L accounts) to be the negative of my difference column.

G/L	G/L Balance	Expected Value	Difference	Reclass
6000	$100	$0	$100	-$100	This reclass for 6000 is what I am trying to recreate. It should be the inverse of the difference column.
5000	$0	$100	$100	$100

At Present I'm attempting to do this:

IF [G/L AC]=505000 THEN 0 ELSEIF [G/L AC]=599999 THEN 0 ELSEIF [G/L AC]=550100 THEN 0 ELSEIF [G/L AC]=822222 THEN 0 ELSEIF [G/L AC]>=899220 THEN 0 ELSEIF [G/L AC]>=556000 THEN -[Difference] ELSEIF [G/L AC]<558299 THEN -[Difference] ELSE [Difference] ENDIF

Developer

Custom Tools

Accepted answers

MarqueeCrew

How about trying this:

IF [G/L AC] In (505000, 599999, 550100, 822222) Or [G/L AC] >= 899220 THEN 0

ELSEIF [G/L AC]>=556000 Or [G/L AC] < 558299 THEN -[Difference]

ELSE [Difference]

ENDIF

if your gl is a string, you need to wore the gl values. If your difference is a string, you need to tonumber(replacechar(difference,"$,",'')) and store it in a double. This would remove any pesky $ or commas.

cheers,

mark

All comments

MarqueeCrew

How about trying this:

IF [G/L AC] In (505000, 599999, 550100, 822222) Or [G/L AC] >= 899220 THEN 0

ELSEIF [G/L AC]>=556000 Or [G/L AC] < 558299 THEN -[Difference]

ELSE [Difference]

ENDIF

cheers,

mark

davidlocke

Thank's for looking at it, but now all G/L accounts the inverse, except for the zero accounts. Also, because of the sensitivity I can't share the workflow, but here are a few accounts of each type that I copied and pasted from my Alteryx result.

Entities	COGS Category	G/L AC	P&L Recon Amt	GL TB Amt	Difference	COGS Reclass
1000	COGS	520310	-3952.05	24933.32	-28885.37	28885.37
1000	Reclass	556000	0.00	0.00	-52164998.55	52164998.55
1000	SG&A	610500	2306.81	23119.21	-20812.40	20812.40

You can see the answer provided thus far, regardless of the account number it is taking the inverse of the difference. I would expect it to look more like this:

Entities	COGS Category	G/L AC	P&L Recon Amt	GL TB Amt	Difference	COGS Reclass
1000	COGS	520310	-3952.05	24933.32	-28885.37	-28885.37
1000	Reclass	556000	0.00	0.00	-52164998.55	52164998.55
1000	SG&A	610500	2306.81	23119.21	-20812.40	-20812.40

To further clarify the purpose of this exercise, we track expenses for financial statement purposes on a cost center basis. However, for tax purposes we need that data reallocated into G/L accounts, but we still need to tie to financials. The P&L recon amount is the amount that is recorded in top-sided G/L account per the COGS line on our financials. The GL TB amount is the amount that is actually currently in the trial balance.

My exercise is two-fold. One, I need to increase or decrease the GL TB amount in COGS accounts (5 series accounts, excluding our tax accounts 556000-558200) to make them tie to the P&L Recon amount. Two, I need to reclass amounts idenified in the P&L recon as COGS, but are in a SG&A account on the GL TB (6-8 series account) into one of our Tax COGS Reclass accounts (556000-558200) so that the sum of all 5 series accounts ties to the COGS line on the financials and all the 6-8 series accounts tie to the SG&A amounts on financials. Any variances are reclassed to settlement accounts.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703