5 Replies Latest reply on Nov 24, 2017 2:29 AM by Håvard Magnussen

# Calculate difference between table calculation and SUM()

I have workbook where I compare the running total for a list of transactions by date vs a list of precalculated balances. The first column is a basic table calculation using running total and the other column i a simple SUM(). Now I want to calculate the difference between them, but I simply can't figure out how to.

• ###### 1. Re: Calculate difference between table calculation and SUM()

hi Håvard,

So without seeing your workbook it's hard to give you an exact solution but hopefully the below helps...

Although we can use the quick table calculation for running sum, this is actually just a Tableau short cut for a formula (they've made the most common ones available in quick table calc). So the actual formula is

[Running Sum Sales]

RUNNING_SUM(SUM([Sales]))

so we can just create another calculation using this to get the difference

[Running Sum Sales] - SUM([Sales])

Hope that makes sense and helps...let me know if not

• ###### 3. Re: Calculate difference between table calculation and SUM()

Thanks for the reply. I had sort of tried this already.

My problem is - that I of course should have mentioned - that I have two sets of data, separated out by a column "Dataset". For Dataset 1 (transaction) I need to calculate a running total to arrive at a balance. For Dataset 2 (balances), I just list the values.

So basically what I need to do is to make a conditional statement something like this:

Calculated field for dataset 1 (Transactions):

IF [DATASET]="BATransactions" THEN RUNNING_SUM(SUM([NETQC])) END

Calculated field for dataset 2 (Balances):

IF [DATASET]="BAHoldings" THEN [NETQC] END

Then in theory I should be able to do this:

Calcualated field for difference (Difference):

[Transactions]-[Balances]

This, however, fails miserably on the first calculated field with this error message:

"Cannot mix aggregate and non-aggregate or results in 'IF' expressions"

• ###### 4. Re: Calculate difference between table calculation and SUM()

hi Håvard,

If you want a bit about why you can't mix aggregates and non aggregates...this might help Answer - Quora

With regards your calculations, you can embed the condition inside the aggregate like this...

RUNNING_SUM(SUM(IIF([DATASET]="BATransactions", [NETQC],NULL)))

Then your variance would be something like

[Transactions]- SUM([Balances])

Hope that helps

1 of 1 people found this helpful
• ###### 5. Re: Calculate difference between table calculation and SUM()

Thanks again.

I actually solved it myself. Created two new calculated fields. Than ran a simple SUM and a RUNNING_SUM on each separately. Then all done. But your solution with embedded condition was cool!