Need help with query for adding totals

[swebb]

All,

I have a finance report I'm working on looking for total values that roll up in to each other (E.g., Total Revenue - Total Expenses = Net Operating Income) , problem is I have it all in one table because of the way the end user wants it structured in the matrix layout. I think that a custom column will work best here, for the needs of the user. Here's what I have, but it is throwing errors:

if [accountNumber] >= 410000 and [accountNumber] <= 450000 then [Converted Totals]
else if [accountNumber] > 450000 then [Converted Totals]
else null

The account number column corresponds to the type ("Expense", "Revenue", etc.) my issue is I don't know how to sum each column value that corresponds to 410000, 420000, 430000, 440000, and 450000 --- I hope this makes sense. Let me know if not and I'll do my best to clarify.

[jennratten]

Hello - your logic works for me.  In order for this to work as is, your accountNumber and Converted Totals columns must be numeric data types and must not contain text.  What kind of error are you getting?  Can you post a snip of your data?

[swebb]

@jennratten Thanks for pointing out the obvious, lol, my data types were wrong! Follow up question, I need to do mathematical operations against these numbers. Example, for everything identified as being between 410000 and 450000, add those corresponding values and put them in the new column, additionally, anything greater than 450000 needs to be added as a different value, but in the same new column. Does that make sense? 

[jennratten]

No worries.  🙂  Yes, this makes sense, but can you please post an example of what you would like the resulting table to look like?  I would like to make sure we are on the same page about what you are trying to do.  For example, if you add up everything in the column that is between 410000 and 450000 and then add it as a new column, you will have duplicate values in rows.  Is that what is expected?