3 Replies Latest reply on Apr 19, 2017 12:00 PM by keerthi baikan

# Difference of a running total reset on year

Hello,

I am working with some data that is provided quarterly and summed year-to-date. For example, if in Q1 2016 I made \$100 and in Q2 I made \$120 my data would read:

Q1 2016: \$100

Q2 2016: \$220

I want to have Tableau take the difference of this running total to change my running total to values for individual quarters. I've tried using a quick table calculation to take the difference but since my data effectively resets in Q1 each year, my Q1 differences are always incorrect. Again, as an example, if Q4 2015 is recorded as having \$410 (cumulative for that year, even if it only made \$90 that particular quarter) and Q1 2016 is \$100, after I take my table calculation my data will look like this:

Q4 2015: (left blank because it's first)

Q1 2016: - \$310

Q2 2016: \$120

Is there a way I can do a difference-from-previous while having Q1 stay the same every time the year resets? I would like my data to look like this after taking the difference:

Q4 2015: \$90

Q1 2016: \$100

Q2 2016: \$120

I've attached a sample workbook that provides test data to illustrate the problem.

Thanks,

Dan

• ###### 1. Re: Difference of a running total reset on year

I would add a workbook but this is 9.3 and I only have 10.+ on this current machine. But change the Table Calculation Settings.

Click the pill and "Edit Table Calculation". this one works for me:

Hope this helps,

--Matt

• ###### 2. Re: Difference of a running total reset on year

Hi Daniel,

Please create a calculation "Quarter sales" as shown in image below and use "compute using" as per your data organization.

1 of 1 people found this helpful
• ###### 3. Re: Difference of a running total reset on year

I think this calculation works, but I'm not happy with the condition. My hope was to be able to identify Q1. This is relying on increasing values, except at the start of each year.

(Table Across)

If Sum([Dollars]) < Previous_Value(Sum([Dollars]))

Or Sum([Dollars]) = Lookup(Sum([Dollars]), First())

Then Sum([Dollars]) Else Sum([Dollars])- Lookup(Sum([Dollars]), -1)  End