Skip to main content
Solved

Variance Percentage Display

  • March 26, 2026
  • 3 replies
  • 22 views
C
Jr Varsity II
Forum|alt.badge.img

Hello, 

I created this year over year variance report for qty sold of a stock item. As you will see in the screen shot below, the calculations in the QTY % VAR column are correct. However, the manager would like to see it as follows: 

Row 1 (for the 021 stock item) the % VAR shows -62.86 (round to 63). He would like to see it as 37% instead of -63%. Is there any way to accomplish this? Here is the formula used for the % VAR column: 

=IIf(     Sum(IIf(Year([ARTran.TranDate]) = Year(Today()) - 1, [ARTran.Qty], 0)) = 0,      0,      (Sum(IIf(Year([ARTran.TranDate]) = Year(Today()), [ARTran.Qty], 0)) - Sum(IIf(Year([ARTran.TranDate]) = Year(Today()) - 1, [ARTran.Qty], 0)))      /      Sum(IIf(Year([ARTran.TranDate]) = Year(Today()) - 1, [ARTran.Qty], 0)) )*100

 

 

Best answer by jhalling52

Hi ​@ChantellBeaty,

I think all you need to do is remove the section in the numerator that is subtracting from the previous year.  Try this:

=IIf( Sum(IIf(Year([ARTran.TranDate]) = Year(Today()) - 1, [ARTran.Qty], 0)) = 0, 0, (Sum(IIf(Year([ARTran.TranDate]) = Year(Today()), [ARTran.Qty], 0)) ) / Sum(IIf(Year([ARTran.TranDate]) = Year(Today()) - 1, [ARTran.Qty], 0)) )*100

    3 replies

    jhalling52
    Jr Varsity I
    Forum|alt.badge.img
    • jhalling52
    • Jr Varsity I
    • Answer
    • March 26, 2026

    Hi ​@ChantellBeaty,

    I think all you need to do is remove the section in the numerator that is subtracting from the previous year.  Try this:

    =IIf( Sum(IIf(Year([ARTran.TranDate]) = Year(Today()) - 1, [ARTran.Qty], 0)) = 0, 0, (Sum(IIf(Year([ARTran.TranDate]) = Year(Today()), [ARTran.Qty], 0)) ) / Sum(IIf(Year([ARTran.TranDate]) = Year(Today()) - 1, [ARTran.Qty], 0)) )*100

    Joe Halling, Strategies Group
      BenjaminCrisman
      Acumatica Employee
      Forum|alt.badge.img+4
      • BenjaminCrisman
      • Acumatica Support Team
      • March 26, 2026

      ​@ChantellBeaty As the previous comment mentioned it might be possible by not subtracting the previous year, but since they are all negatives you can also just multiple by -100 at the end and start with 100- at the beginning and then it will leave you with the positive opposite. You’ll need to use Round(Str, 0) so it rounds to zero decimal places and maybe also add a schema for a field which doesn’t use decimals at all so it shows a whole number with no decimal precision, unless that doesn’t really matter and 37.00 is ok.

        C
        Jr Varsity II
        Forum|alt.badge.img
        • ChantellBeaty
        • Author
        • Jr Varsity II
        • March 26, 2026

        Thank you both so much for your help. The rounding is ok with the formula provided. 

          Terms & ConditionsCookie settingsAccessibility statement
          About Acumatica ERP system
          Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
          © 2008 — 2024  Acumatica, Inc. All rights reserved