topic Re: Need assistance on formula in QlikView

Need assistance on formula

Thu, 26 Feb 2015 21:10:02 GMT

I have the follow pivot table. For my final total, I need the difference between Revenue and Expenses.

When I ask for a subtotal by TYPE, (rev and exp) it adds the two totals together.

Re: Need assistance on formula

eespiritu — Thu, 26 Feb 2015 21:31:16 GMT

Hi Denise,

Have you tried add a new expression with the formula to obtain the difference? I don't know if is possible change the "Subtotal" or "Total" (QV default) to other expression.

You can add a new expression with something like this:

sum(TOTAL <Dimension>[Revenue]) - sum(TOTAL <Dimension>[Expenses])

I hope that this can be useful.

Regards,

Enrique

Re: Need assistance on formula

Anonymous — Thu, 26 Feb 2015 21:38:34 GMT

The easiest way is to keep Expenses data negative.
An alternative, use conditional expression based on dimensionality, which is 0 for total:
if(dimensionality()=0, <expression for total>, <expression for rows>)

Re: Need assistance on formula

Fri, 27 Feb 2015 00:52:07 GMT

Hi Denise

Probably you can do like this.

Create a formula for your value field depend on the type

Formula name(Variable) : eModValue

if(Type='Revenue',value,-1*value)

Call the formula in the pivot table expression.

sum($(eModValue))

In that way, Pivot table will subtract the values and gives you the expected answer.

Regards

Harshana

Re: Need assistance on formula

Fri, 27 Feb 2015 15:25:34 GMT

Awesome! This did it.

Thank you.

Re: Need assistance on formula

Tue, 03 Mar 2015 01:27:38 GMT

Hi Denise

I am happy that is work you.

You can make that as a correct answer

Regards

Harshana