4 Replies Latest reply on Nov 14, 2018 10:01 AM by Kevin Das

# Running Total On Dual Axis - How can I show data for all months for one measure (budget) but not all months for another measure (sales)

Hi,

I need to create two charts showing sales vs budget. One chart will show monthly sales vs monthly budget, by month. This is simple enough to create:

The second chart needs to look almost identical but show running totals.

I only want to show running total for sales up to the latest month with sales (October) but the running total for budget should show all months.

I cannot see how to remove the running totals of sales for months after October without also removing the running total for budget:

I have attached a simplified example workbook hoping someone can help - I am sure I am missing something very simple!!

Many thanks for any help

Kev

• ###### 1. Re: Running Total On Dual Axis - How can I show data for all months for one measure (budget) but not all months for another measure (sales)

see the attached

I converted the table calcs to actual formulas and made this change to the running sales

it now returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Running Total On Dual Axis - How can I show data for all months for one measure (budget) but not all months for another measure (sales)

Hi Kevin,

Create a calculation like below

Calculation 2:

IF ATTR(YEAR(TODAY()))=ATTR(YEAR([Month])) AND

ATTR(DATEPART('month',TODAY()))-1>=ATTR(DATEPART('month',[Month]))

THEN

RUNNING_SUM(SUM([Sales]))

END

Replace the running sum of Sales with the calculation 2 and you will get the O/P:

Check the final O/P:

Hope this helps

attached workbook

1 of 1 people found this helpful
• ###### 3. Re: Running Total On Dual Axis - How can I show data for all months for one measure (budget) but not all months for another measure (sales)

You can tell the running sum to stop after a certain month.

In the attached I made a running sum calc of my own.  (You can't do this with a quick table calc.)  To get the syntax of the quick table calc, just drag your quick calc into your calc editor, and tableau will expand the underlying syntax for you.

Then I made a LOD calc to find the last date with sales data.  (See [Last month with...].)  Then I used that to insert nulls for all the months after that date.  (If you don't give an ELSE clause with an IF statement, the default for ELSE is null.)

I put that value on labels.  You can just replace your quick calc for running sales with this one to get the viz you want.

1 of 1 people found this helpful
• ###### 4. Re: Running Total On Dual Axis - How can I show data for all months for one measure (budget) but not all months for another measure (sales)

Thank you so much Jim, Naveen and Joe

This is just what i needed.

All the best

Kev