Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need assistance on formula

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

5 Replies
eespiritu
Creator
Creator

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Awesome!  This did it.

Thank you.

Not applicable
Author

Hi Denise

I am happy that is work you.

You can make that as a correct answer

Regards

Harshana