4 Replies Latest reply on May 10, 2017 7:46 AM by Steffem Lerbs

# Running Total result in total Column without using running_total Table Calculation

Good Morning,

i am using Tableau now for a few year and usually find my way around. However this time i would like to ask for your help for the following situation.

i need to show in a simple table the running total of monthly data and show for every quarter the subtotals. the challenge is that the data is already pre calculated in the database as a running total. my figures are now Jan 7000 Feb 8000 Mar 9000 April 100000. I need to "manipulate" the sub totals in a way that it always shows the last month of the quarter with values in it. so the Total for Quarter 1 needs to show 9000 and Quarter 2 shows 10000. it would be nice if also the subtotals for Quarter 3 and Quarter 4 can show the latest month's result which in this scenario would be 10000 as well.

I read the nice "drawing with numbers" blog about Grand Totals and came up with the following formula:

IF MIN(MONTH([Month])) != MAX(MONTH([Month])) THEN

SUM(IF MONTH([Month]) = 3 THEN [Performing houses TY] END)

ELSE SUM([Performing houses TY])

END

The issue is that the 3 in the second line of my formula is hard-coded and i dont get it to work to be dynamically.

thanks for your help in advance. I know it is better to have a work book attached however i havent found a smart way of doing that as it contains sensitive data for the company i work for.

Steffen Lerbs

Nachricht geändert durch Steffem Lerbs i am adding the workbook. up to line 5 this is what i need to display in the report. All the calculations below are my tries to show the subtotal by quarter which is supposed to be the last months result of performing houses.

• ###### 1. Re: Running Total result in total Column without using running_total Table Calculation

Hi

It is difficult to know exactly what you want without seeing the data but have you considered adding a Parameter to the sheet for the Month number and inserting that parameter name in you formula (Note you could also use Month(today()) or a date range based on today or the parameter

Let me know if this helps

Jim

• ###### 2. Re: Running Total result in total Column without using running_total Table Calculation

Hi, Stteffem

I'm a bit of confusing of what exactly you need.

My guess is if the month is the last month of a quarter, you show it as quarter value, so you can use % to get the last of a quarter dynamically. Please see below highlighted code:

IF MIN(MONTH([Month])) != MAX(MONTH([Month])) THEN

SUM(IF MONTH([Month])%3=0 THEN [Performing houses TY] END)

ELSE SUM([Performing houses TY])

END

Not sure if it helps you or not, let me know if any help.

ZZ

• ###### 3. Re: Running Total result in total Column without using running_total Table Calculation

Hello Zhouyi,

i need to give an overview showing 12 month and 4 quarters. the rows need to show the values for last year and this year. in adition i need to calculate targets and variances between the years. the challenge i have is to present the quarterly figures as the last month in the display. for quarter 1 it would be month march. for quarter 2 i need to show the month of april as i dont have data for may this year yet. so unfortunately it isnt always month 3.but is the last month with data in a quarter.

if a quarter has no data for this year as quarter 3 and 4 it should show the subtotal from quarter 2 (or the last quarter with data)

i will however test the formula the way you have described it with the % and see what the impact is

thank you for looking at this !!

Steffen

• ###### 4. Re: Running Total result in total Column without using running_total Table Calculation

Hi Jim,

the formula i show is only working for the month 3 but i put that in to display which value i would like to see in the subtotals for quarter 1. so i dont want the user to select the subtotal value via a parameter.

thanks

Steffen