4 Replies Latest reply on Dec 8, 2017 11:12 AM by Deepak Rai

# Calculating totals PRIOR to a given year

Hi all. This one is driving me absolutely crazy.

I am trying to create a worksheet that shows students' earned GPA hours and quality points PRIOR to a certain year.

Let's say that year is 2016. Student 111111 would have had 3 total GPA hours and 12 quality points prior to 2016. Student 222222 would have had 10 GPA hours and 35 quality points.

What calculation can I write that will sum values per ID prior to a given year? • ###### 1. Re: Calculating totals PRIOR to a given year

RUNNING_SUM(SUM([GPA Hours])) - SUM([GPA Hours]) will work if your view is structured as you show above. The RUNNING_SUM table calculation will need to be calculated over the [Year] dimension.

• ###### 2. Re: Calculating totals PRIOR to a given year

Hey Brent, thanks for the quick reply. I'm not sure I fully understand this solution. Will this "break" on ID? Also, where am I defining the year for which I want a prior total?

• ###### 3. Re: Calculating totals PRIOR to a given year

Hi Sean. This solution is a table calculation - it will break on ID, depending on how you configure it. My solution is based on your visualization being structured as you showed in your original post (ID and Year). For each ID and Year, my formula calculates the running total of [GPA Hours] and subtracts the [GPA Hours] for the year on that row, in essence giving you the total [GPA Hours] for all years prior to the given year. For instance, for the row containing year 2016, my formula will give you the total for all hours through 2015 for each ID.

If you are unfamiliar with table calculations in Tableau, you should definitely learn more - there are quite a few good tutorials/resources out there. And here's a good one from Andy Kriebel that has really helped me: Tableau Tip Tuesday: Table Calculations Overview

• ###### 4. Re: Calculating totals PRIOR to a given year

Create a  integar parameter and add Years to that like 2010, 2011, 2012....2016, 2017, Show parameter Control

Use This:

This would give u GPA HOURS PER ID

{FIXED [ID]:SUM(IF Year<Year([Year Parameter]) THEN GPA HOURS END) }

For Quality Points use this;

{FIXED [ID]:SUM(IF Year<Year([Year Parameter]) THEN Quality Points END) }

Now when You will select any Year from parameter, The Result would be calculating for YEARS PRIOR TO SELECTED YEAR.

Thanks

Deepak