2 Replies Latest reply on Jan 8, 2017 2:47 PM by Chris McClellan

    Net Income Calculated Fields Question. Pretty easy I hope

    Josh Marks

      Hey everyone, this should be a pretty easy one I hope.

       

      I have the following data table:

      I'm trying to create calculated fields such as:

      Net Income = Revenue - Expenses

       

      I have been able to accomplish this by creating 3 calculated fields

      REV => if AccountType = "Revenue" then [amount] END

      EXP => if AccountType = "Expenses" then [amount] END

      NETINCOME => Sum(REV) - sum(EXP)

       

      And this is OK, but it doesn't scale very well. If I have 10 calculated fields I want, I end up creating 30+ variables. So, I don't love it.

       

      Does anyone have a solution that required less calculated fields?

       

      Thanks so much!!!

      Josh

        • 1. Re: Net Income Calculated Fields Question. Pretty easy I hope
          Dan Huff

          The two solutions here are either to do what you are doing in created calculated fields or to unpivot this data in the database itself.

           

          It would likely be a better, more usable structure for future reports to have each "Account Type" as a column. In places where the School/SchoolType/Account dont have a value for a given Account Type, you would just end up with a null in the new structure.

           

          Dan

          1 of 1 people found this helpful
          • 2. Re: Net Income Calculated Fields Question. Pretty easy I hope
            Chris McClellan

            If you want to use REV, EXP and NETINCOME as 3 values, then that's the way to do it.

             

            If you only want NETINCOME you could do something like this:

             

            NETINCOME => Sum(if AccountType = "Revenue" then [amount] END) - sum(if AccountType = "Expenses" then [amount] END)

             

            But basically when you have the data in that structure (ie Acct Type and Value as 2 columns), then you have to split it out when you want to calculate between different types like this.