1 2 Previous Next 19 Replies Latest reply on Nov 8, 2013 9:45 AM by michael.chan.0

How to create a calculated constant value line graph

Hi all,

I’m trying to build a simple bar chart of rework hours incurred (by month) in a one year snapshot, with a line graph representing a goal line. The goal line should be a calculated field which is 50% of the previous fiscal year’s overall rework hours. (The goal is to reduce hours by 50%). This line graph needs to be the constant value across all months. This way, we can see which current fiscal year months’ rework hours were under the 50% goal, and which month’s rework hours were above the goal. Having a “Previous FY Average” Bar would be a plus (easily done once a FY table calculation could be made to sum those hours), and additionally, we’d love to be able to have the month bar charts change color if it’s over the goal line (color red) vs if that month was under the goal line (color green).

My problem is that my line graph calculated field is 50% of each respective month, not a constant number across ALL months. Does anyone know how to get achieve this?? Perhaps there’s a way to just add a line graph with a constant value that I can assign it? *Note: we use a fiscal year beginning in September for our company. As I understand it, FY is not recognized in table calculations, and another calculation may need to be done – which I’m not sure how to do…

I’ve attached a sample workbook to show what I’ve done thus far. I’ve also attached an example Excel chart to better illustrate the view of what I’d like the chart to look like when all is said and done. (These numbers in the Excel chart are not the same as what’s in Tableau, it’s for visualization purposes only)

• 1. Re: How to create a calculated constant value line graph

Eric--

The following should solve the issue. I first created the Average calculation which allows me to find the annual average. Second, I created the 50% of Previous Average calculation which allows me to grab and half the previous year's average. This has to be done in separate calculations as the two calculations require different compute using settings.

Hope this helps,

Dan

1 of 1 people found this helpful
• 2. Re: How to create a calculated constant value line graph

Dan,

This is perfect! The only other thing that I'd like to know about, is if there is a way to color the bars on the FY 2013 year red if it's above the 50% Average Line, and green if it's below the 50% line??

Thanks so much for this! We use a lot of these types of graphs so these two calculations will go a LONG way!

• 3. Re: Re: How to create a calculated constant value line graph

Consider it done

Dan

• 4. Re: How to create a calculated constant value line graph

This is a thing of beauty! I see that you just created another calculated field and applied it to the colors...I don't suppose you could just list out the steps of how you did that? (The only problem with Tableau, is that unless you're intimately famliar with the process of creating some of these things, it can be a bit hard to reverse-engineer the steps to making it happen). The calculated field itself is obvious, but how did you split out the True/False requirement in order to assign a color to True and a color to False?

Thanks so much though Dan, this is perfect!

• 5. Re: Re: How to create a calculated constant value line graph

Fair enough. I will list the steps for the entire view here:

1. Create the Average calculation
2. Add it to the view and set it to Pane Across -- this gives us our annual average
3. Create the 50% of Average calculation
4. Add it to the view, right click the pill, and hit edit table calculation
5. From here, you will notice that there is a dropdown in the edit dialog. This is due to the fact that the 50% Average calculation relies on another table calculation. Because of this, we have to use the edit dialog here to set the proper compute using.
6. Ensuring that the 50% Average calc is shown on the dropdown, hit compute using, select advanced, and mimic what I have in the attached workbook.
7. Setting this at the level of years determines the level of granularity which is applied to our -1 offset in lookup. In other words, we will look up the previous year's average in each month of the current year.
8. Create the color calculation
9. Since all the underlying table calculations have already been set, you do not need to set anything up for this field. Click the Sum Hours Spent pill and place the color calculation on color for this field.
10. You notice we get three colors to which you can assign values (double click the legend and assign them). True and False come from the fact that the color calculation is a boolean statement. The Null is the result of there not being any values for the 50% Average field in the first year in the view.
11. Finally, change the color of the 50% Average bar to something else.

I have also added a different version of the workbook that uses a reference line to show the average. It is slightly cleaner as it 1) draws the line across the whole pane and 2) allows you to dual axis in a different measure if needed.

All I did to accomplish this was drag the 50% Average calc to the Detail shelf, right click the hours spent axis, and add a reference line. You can see my settings there.

Hope this helps,

Dan

1 of 1 people found this helpful
• 6. Re: How to create a calculated constant value line graph

Dan,

This is extremely helpful -- I can't thank you enough for your time of writing this out for me. I really, really appreciate it!

Thanks!

-Eric

• 8. Re: Re: How to create a calculated constant value line graph

Dan,

I wanted to walkthrough your directions step-by-step from scratch just to ensure that I was able to replicate and understand how to create these graphs, or graphs like them, in the future. The only thing that I’m seeing on my reproduction is that the reference line (good tip by the way) and True/False colors are being applied to both Fiscal Years, when I’d just like to see them applied to FY 2013, like you have in your latest example. I’m sure it’s something small that I didn’t do along the way, but do you know what steps to take to make this change?

This is my last request for this, I promise!

• 9. Re: Re: How to create a calculated constant value line graph

Do not worry about posting again. This is a fairly difficult table calculation.

Here is a list of the problems and how you can recognize them. I am able to recognize them without looking at the calcs as I have a lot of experience troubleshooting things like this. As you play with these more and more, you will start recognizing these symptoms and their solutions.

• Only the first bar is colored blue for Null. This tells me that the lookup is running at the level of month and not year. There are two causes to this: 1) you did not set the at the level to Year or 2) the order of the fields in compute using is incorrect
• In this case, it is the fact that the order of the compute using is incorrect. You have month above year in the 50% previous addressing in the compute using settings. This must be reverse. In general, the order of this can be thought of in the following way: left to right equals up to down. In other words, year should be above month in addressing because year is the leftmost pill on columns. The order here is crucial.
• Changing this on both the 50% of Previous and ColorChooser fields will fix the issue.
• The reference line is being drawn in both panes. Furthermore it is the same number in each pane. This tells me that the calculation driving the average is being run table across rather than pane across. Setting it to pane across would result in two different averages unless the data is exactly the same in the panes
• For both the ColorChooser and 50% of Previous pills you need to right click, click Edit Table Calculation, change the dropdown to Average, and change table across to Pane Across. This will give us an average for each year rather than an average of both years combined.

If some of this vocabulary is confusing, you can always watch my quick video about table calculations. I try to address a lot of the vocabulary that I have been using throughout this thread. You can find it in the Advanced section of our training videos. It is called Table Calculations.

Hope this helped,

Dan

• 10. Re: Re: How to create a calculated constant value line graph

Not a problem sir! Post whenever you have the time

• 11. Re: Re: How to create a calculated constant value line graph

Post edited above. Thanks for your patience.

Dan

• 12. Re: Re: How to create a calculated constant value line graph

Dan,

I appreciate your understanding on the matter -- I admin the quality software within our company and am frequently asked questions by users so I can appreciate the situation .

I was able to make the necessary adjustments on my graphs (and I was wondering why only the first month was a null value as well) but now that I know that the order of Year>Month is crucial, I'll note that in my personal documentation for how to develop these charts for future use.

I looked over some of those videos when our company was first introduced to the software, but now as I'm building some of these and becoming more familiar with Tableau, I'll definitely revist them again, specifically the one on Table Calculations.

Again, I really appreciate the time you've spent helping me on this issue, the calculations will go a long way for our company (and particularly our Quality Department!). Many thanks sir!

• 13. Re: How to create a calculated constant value line graph

Dan,

Is there a way to keep these calculations and remove 2012 from the display? Thanks

• 14. Re: How to create a calculated constant value line graph

Absolutely. Make a calculation that is the following formula: index()!=1

This reads as index is not equal to one. In other words, we will be able to hide everything after the first year.

Add this calc onto the level of detail, right click it, click edit table calculation, and hit advanced on the compute using dropdown. Once open, move Year of Month/Year to Addressing while leaving Month of Month/Year in Partitioning. This will tell our index to run along our years such that FY 2012 gets an index of 1 and FY 2013 gets an index of 2.

Then all you need to do is drop this cal on the filters shelf and keep the True value.

I hope this helps,

Dan

1 2 Previous Next