Need help on a formula

[#1]

On the table below, under each column, I want to subtract the Expense total from the Revenue total.

The totals under both of these are set under Presentation (Show Partial Sums). 

How do I get a total for the difference between the two types:

Example:  I want to show:   Revenue     242,781.64

                                             Expenses   913,268.59

                                            Net Use     (670,486.95)

I

[JonnyPoole · Accepted solution]

If your expression is something like this  sum(numbers)  , then you can change it to something like this

if(  Dimensionality()=0 , 

     sum( {$<Type={Revenue}>}  numbers) - sum( {$<Type={Expenses}>}  numbers) ,

     sum(numbers)

)

This will give you a different formuala for the column footer total (dimensionality=0) and your regular expression for every other cell.

[rubenmarin]

Hi Denise you can use:

If(Dimensionality()>0, Sum(Value),

Sum({<Type={'Revenue'}>} Value) - Sum({<Type={'Expenses'}>} Value)

)

Change 'Value' for the field you are using

[#4]

Where do I put the expression?  Here is how I got the total revenue and total expenses.

I just asked for partial sums  under the presentation tab under Acct#.

Which gives me the totals for all Revenue and Expenses.

Then under the Presentation tab I asked for partial Sums under the Type field.

How do I enter a formula in the (Label for Totals)?

[rubenmarin]

Hi Denise, it should be your expression in the expressions tab

[#6]

Hi,

try like this

If(Dimensionality()>0, Sum(Value),

fabs(Sum({<Type={'Revenue'}>} Value) - Sum({<Type={'Expenses'}>} Value))

)

[JonnyPoole]

On your expression tab you will have an expression that calcualtes the cell values and the totals... it is that expression that you will change with the guidance from before.

The dimensionality()=0 condition will capture cells for TOTALs and calculate them differently.  THere is no separate expression just for totals.

so far you have done everything correctly, just replace the formula of the expression on the expression tab and see what happens next...