topic Re: Need help on a formula in QlikView

Need help on a formula

Wed, 22 Oct 2014 14:37:15 GMT

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)

Re: Need help on a formula

JonnyPoole — Wed, 22 Oct 2014 14:41:31 GMT

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.

Re: Need help on a formula

rubenmarin — Wed, 22 Oct 2014 14:42:42 GMT

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

Re: Need help on a formula

Wed, 22 Oct 2014 15:24:54 GMT

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)?

Re: Need help on a formula

rubenmarin — Wed, 22 Oct 2014 15:27:54 GMT

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

Re: Need help on a formula

Wed, 22 Oct 2014 15:29:44 GMT

Hi,

try like this

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

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

)

Re: Need help on a formula

JonnyPoole — Wed, 22 Oct 2014 15:57:37 GMT

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...