Create an negative value (inverse) of another field

[davidlocke]

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

[MarqueeCrew · Accepted solution]

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

[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. 

[davidlocke]

I figured it out.  I don't think there was anything wrong with your response.  I think I just messed up and AND vs. OR.  It seems to be working now THANKS!