11 Replies Latest reply on Jan 7, 2018 8:24 AM by yamuna.kosireddi

# Calculating running total

Hi everyone,

I am trying to compare cumulative sales  between budgeted sales and sales .

My x- axis had the months from Jan -dec in financial year format and i have budgeted sales for all 12 months but sales has only up to dec. when iam calculating running total for sales it summing up value till march (can see from attached screen shot ).

I want to show running total for sales up to December only.

Any help on this?

Yamuna

• ###### 1. Re: Calculating running total

Hi Yamuna,

As I can see it is a sorting problem as the months are showing alphabetically instead of showing datasource order as below:

change the order from Sort by: Alphabetic to Data Source Order and check

Or

as below:

hope this helps.

• ###### 2. Re: Calculating running total

Hi Yamuna,

Just check the default FY month of your date field. I guess FY month has been set to April. Change it to January then I guess issue will get resolve. Let us know if you this help else share your workbook

Mahfooj

• ###### 3. Re: Calculating running total

Hi Yamuna,

As I understand from your question it sounds like you do not want your orange line (actual sales) to continue horizontally to the end of the chart but to stop in December (up until your last recorded sales). Is that correct?

If so, this is possible by creating a calculated field that returns "Null" marks at these point on the canvas.

In the attached workbook I have created a calculated field that explicitly returns a running sum of sales only when there is data present, otherwise it returns Null:

Then by using this field; "Running Sum", on your row shelf rather than your "Sales" field, you should see that line now stops in December.

Here is a knowledge base article that explains this:

Excluding Null Values from Running Total | Tableau Software

Hope this helps

Alex

4 of 4 people found this helpful
• ###### 4. Re: Calculating running total

HI Mahfooj,

I have the Budgeted sales data for FY year format like 2017-April till 2018-march.i need to represent my graph in FY format like Apr to March as seen in the screenshot. if iam changing to January it is working fine.

• ###### 5. Re: Calculating running total

Thanks Alex,

Correct solution for my requirement but we cant add aggregate functions with another  if condition right ... here i want to use this calculation in some other if condition.any other way to use

Thanks

Yamuna

• ###### 6. Re: Calculating running total

Thanks Yamuna

You should be able to now use the result of this running sum field in another calculated field to perform a test on its result.

For example, lets say we want to test the point that your cumulative sales pass a certain threshold. We can create a calculated field that references the Running Sum and tests it against the value of a parameter:

Tableau will then evaluate this at every mark on your canvas. In the example I have created a dual axis chart to show how the mark shapes change as I adjust the threshold value:

Hope this helps. The example is attached.

Alex

1 of 1 people found this helpful
• ###### 7. Re: Calculating running total

Alex,

When iam creating calculated filed with respect to parameter using running sum calculation filed,it was throwing error saying that cant aggregate in if condition.

• ###### 8. Re: Calculating running total

Do you have a screen shot of the calculated field you have created, or a workbook you can share as that would really help me to understand what may be going wrong?

• ###### 9. Re: Calculating running total

Hi Alex,

Due to data security iam not able to share workbook but attaching some of the calculated filed.

Thanks

Yamuna

• ###### 10. Re: Calculating running total

Try performing the logic statement before any aggregation of your sales:

and then use that within the running sum calculation instead of "Sales":

This is because after aggregation, Tableau cannot see the level of detail necessary for your logic statement.

Example attached. Hope this helps

Alex

2 of 2 people found this helpful
• ###### 11. Re: Calculating running total

Thanks a  lot Alex.