10 Replies Latest reply on Sep 30, 2016 11:54 PM by Stergios Hetelekides

# Taking Running Total and Calculating Differences

Hi,

I am new to Tableau. I have some data that contains a running total of some data. I am trying to create a bar chart that graphs the difference from one row to the next. I thought creating a calculated field would do the trick, but I cannot figure out the correct calculation to achieve this. Below is an example of what I'm looking for.

Running Total of Some DataCalculated Field (Difference)
1.01.0
1.20.2
1.80.6
2.70.9
2.90.2
3.80.9
4.50.7
5.10.6
5.10.0

I've tried many different calculations and I cannot get it to work. The one that I believe should do the trick is:

```[Running Total of Some Data]-PREVIOUS_VALUE(0)
```

But I get the error, "all fields must be aggregate or constant when using Table Calculations or fields from multiple data sources." I'm sure this is not how PREVIOUS_VALUE works. I'm probably misunderstanding something crucial. Maybe my field's description will help?

Running Total of Some Data

 Role: Continuous Measure Type: Database column Remote column: [table].[running_total] Remote type: Double-precision floating-point number Default aggregation: Sum Status: Valid

If you know of any other efficient way of producing my desired result (bar graph with difference), please let me know! Performance matters.

Thank you for any help!

• ###### 1. Re: Taking Running Total and Calculating Differences

Try [Running Total of Some Data] - LOOKUP([Running Total of Some Data],-1)

This thread may help explain what Previous_value is doing: PREVIOUS_VALUE vs LOOKUP([expr],-1)

• ###### 2. Re: Taking Running Total and Calculating Differences

Unfortunately that is resulting in the same error. Thank you for trying though, I appreciate it.

• ###### 3. Re: Taking Running Total and Calculating Differences

Ok, so your running sum is actually precalculated? In which case try this:

SUM([Running Total of Some Data]) - LOOKUP(SUM([Running Total of Some Data]),-1)

• ###### 4. Re: Taking Running Total and Calculating Differences

Yes, my running sum is pre-calculated as shown above.

For some reason I am just getting null for each row when entering that calculation.

Thanks again.

• ###### 5. Re: Taking Running Total and Calculating Differences

OK, so you'll need to troubleshoot what is happening calc by calc to see where your NULL is returned.

You should already have the [Running Total of Some Data] summed in your view.

Put this formula also into your view: ZN(LOOKUP(SUM([Running Total of Some Data]),-1)). I have wrapped it in ZN so any Null values (only the first should be null) is returned as 0 (zero).

Assuming both of those formulae are good combining them should work: SUM([Running Total of Some Data]) - ZN(LOOKUP(SUM([Running Total of Some Data]),-1))

If not there must some information missing causing it to fail, in which case please upload a twbx file to it can be investigated.

1 of 1 people found this helpful
• ###### 6. Re: Taking Running Total and Calculating Differences

Andrew,

That is very close! It is kind of working. I've noticed that using MAX instead of SUM yields the best result because my [Running Total of Some Data] data is continuously being incremented. The maximum value represents the value for that time period.

Doing: MAX([Running Total of Some Data]) - ZN(LOOKUP(MAX([Running Total of Some Data]),-1))

Each one of [Running Total of Some Data] rows is associated with a timestamp. Lets say I have data from 9/25/2016 to today. If I want to display all the data for today, I'm adding a filter and it's almost working, but the first value is the actual value being measured. It should be the value being measured minus the last value of the previous day. This image may be helpful.

COLUMNS: DATEPART('hour', [EST Timestamp])

ROWS: MAX([Running Total of Some Data]) - ZN(LOOKUP(MAX([Running Total of Some Data]),-1))

FILTER: Have only today selected for the timestamp.

Thank you again. I'm very close to achieving what I want.

• ###### 7. Re: Taking Running Total and Calculating Differences

Glad you're almost there. Sounds like you have 1 thing left to do, to change your date filter. Instead of filtering on your date field filter on this calculated field instead: LOOKUP(ATTR([Date]),0)

• ###### 8. Re: Taking Running Total and Calculating Differences

Thanks for your help once again.

Unfortunately I cannot get it to work. I tried creating a calculated filed on my current [Timestamp] column with the calculation you gave me, but all values are null.

• ###### 9. Re: Taking Running Total and Calculating Differences

Hi Stergios,

You should provide some sample data or at least a screenshot of your layout with rows and columns showing.  This will provide more context and make it easier to help you.

Regards,

Ivan

• ###### 10. Re: Taking Running Total and Calculating Differences

Here is a file with some sample data and a Tableau file. The first sheet is correct, the second sheet isn't. Let me know if anything else would help or if I should upload it in a different way.

Thank you.