8 Replies Latest reply on Jun 5, 2017 1:45 PM by Hari Ankem

# Running Total YOY Variance

Dear Community,

I wanted to see if I could get some help with the following.  I have a Running Total line graph with 2017 (current year) and 2016 (last year).  What I want to do is calculate the difference (aka gap) between where we were last year on a running total basis and this year.  For example, based on the information I'm showing, we are projecting to finish at 48k and last year we were at 79k (approx 31k gap).  I want to show in a row underneath the running total, a running total variance in an area chart.  Below is screen shot of what I'm currently working with.

The second question is on the graph below, the information for June to December is a forecast information that I am inputting, is there a way to just change the color of the 2017 line from June to December.  Right now it is in orange all the way, but keeping it Orange for January to May, but put the rest of the line from June to December is say green.

Matt...

• ###### 2. Re: Running Total YOY Variance

I just added the workbook to the original post.

• ###### 3. Re: Running Total YOY Variance

You should add 1 more running total to the rows and then perform a secondary calculation to get the variance of the running totals.

• ###### 4. Re: Running Total YOY Variance

As for the second part of your question, you may have to change the chart to a dual-axis using calculated fields for each year, and then create another calculated field for the coloring logic that you want to apply.

• ###### 5. Re: Running Total YOY Variance

Thanks Hari, not quite what I was looking for, but a great reference for some other things I'm trying to do!

In the bottom row of the worksheet, I'm hoping that it would be just one line, and each point on the line would be the variance between each month in 2016 vs 2017.  If I cross tab the data, and then put in a Running Variance... I did this really quickly in excel, then i get the information that I am looking for.  I unfortunately, don't know how to pull that off in Tableau.

 January February March April May June July August September October November December 2016 3,899 8,440 15,565 22,651 29,478 37,668 44,258 51,401 58,091 64,864 70,913 79,164 2017 3,644 8,230 14,953 23,932 29,209 31,641 33,288 35,337 39,548 42,216 44,538 48,838 Running Variance (254) (210) (611) 1,281 (269) (6,027) (10,970) (16,064) (18,543) (22,648) (26,374) (30,326)
• ###### 6. Re: Running Total YOY Variance

OK. Here you go.

• ###### 7. Re: Running Total YOY Variance

Thanks again Hari, but not still not quite what I'm looking for.  I would like those variances in an area chart that would be underneath the original line graphs.