By using this website, you agree to our Terms of Use (click here)
I am creating ARM financial reports, and one of the columns included in the report is a % Change calculation. As an example, % Change for Actual (A) to Budget (B) would typically be calculated as (A-B)/B*100. However, whenever the divisor (B) is negative, the sign of the % Change is reversed. So, if the Actual was 100 and the Budget was -200, the result is -150%. However, 100 is an increase or improvement over -200, so the % should be positive.
How can I control the calculation for the column so that it effectively accomplishes If B>=0 then (A-B)/B else ((A-B)/B)*-1?
Never mind - I've got it .... = iif(B>=0,(A-B)/B,((A-B)/B)*-1)
Nice one John! 👍
And thanks for sharing your solution.
My question is related to this topic. I am new to Acumatica. I have built my reports with a variance column in my income statement. I am trying to build an IIF statement to essentially give me a favorable or unfavorable variance. For example, my income accounts need to subtract Actual minus Budget but my expense accounts need to subtract Budget minus Actual. This gives a more accurate view of the statement. I thought I could the account type, but that doesn't seem to be a parameter I can choose. Any suggestions?
Hi Joslyn,
What about using @RowCode in your formula to detect what row you're on. Rows before a certain number would be Income, rows after a certain number would be Expense.
Thanks, Tim. I will give that a try!
Solution worked! I had various income statement rows so first I had to get those rows in line with each other so I can use the same column set with any row. I coordinated which rows needed to be subtracted similarly (revenue, GP, Equity Earnings, EBITDA, Net income, etc.). Then I figured out this formula and it worked!
=IIF((@RowCode>4000 AND @RowCode<4100),B-C,IIF((@RowCode=5100),B-C,IIF((@RowCode>7000 AND @RowCode<7100),B-C,IIF((@RowCode=7410),B-C,IIF((@RowCode=7910),B-C,IIF((@RowCode=8500),B-C,IIF((@RowCode=9910),B-C,C-B)))))))
=IIF((@RowCode>4000 AND @RowCode<4100) OR @RowCode=5100 OR (@RowCode>7000 AND @RowCode<7100) OR @RowCode=7410 OR @RowCode=7410 OR @RowCode=7910 OR @RowCode=8500 OR @RowCode=9910,B-C,C-B)
Gives the same result. You don't need to nest IIF statements when they map to the same result - you can just combine the logical conditions.
Online Members
