-
1. Re: How to create a calculated constant value line graph
Dan HuffSep 26, 2013 3:16 PM (in response to Eric Decker)
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
-
Rework Goal Lines.twbx.zip 413.1 KB
-
-
2. Re: How to create a calculated constant value line graph
Eric Decker Sep 27, 2013 6:03 AM (in response to Dan Huff)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
Dan HuffSep 27, 2013 6:32 AM (in response to Eric Decker)
Consider it done
Dan
-
Rework Goal Lines.twbx.zip 412.1 KB
-
-
4. Re: How to create a calculated constant value line graph
Eric Decker Sep 27, 2013 6:44 AM (in response to Dan Huff)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
Dan HuffSep 27, 2013 6:55 AM (in response to Eric Decker)
Fair enough. I will list the steps for the entire view here:
- Create the Average calculation
- Add it to the view and set it to Pane Across -- this gives us our annual average
- Create the 50% of Average calculation
- Add it to the view, right click the pill, and hit edit table calculation
- 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.
- 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.
- 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.
- Create the color calculation
- 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.
- 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.
- 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
-
Rework Goal Reference Line.twbx.zip 412.4 KB
-
6. Re: How to create a calculated constant value line graph
Eric Decker Sep 27, 2013 6:59 AM (in response to Dan Huff)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
-
7. Re: How to create a calculated constant value line graph
Dan HuffSep 27, 2013 7:04 AM (in response to Eric Decker)
Always glad to help.
-
8. Re: Re: How to create a calculated constant value line graph
Eric Decker Sep 27, 2013 9:46 AM (in response to Dan Huff)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
Dan HuffSep 27, 2013 10:11 AM (in response to Eric Decker)
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
- 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
-
10. Re: Re: How to create a calculated constant value line graph
Eric Decker Sep 27, 2013 10:38 AM (in response to Dan Huff)
Not a problem sir! Post whenever you have the time -
11. Re: Re: How to create a calculated constant value line graph
Dan HuffSep 27, 2013 11:31 AM (in response to Eric Decker)
Post edited above. Thanks for your patience.
Dan
-
12. Re: Re: How to create a calculated constant value line graph
Eric Decker Sep 27, 2013 12:30 PM (in response to Dan Huff)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
michael.chan.0 Nov 8, 2013 8:44 AM (in response to Dan Huff)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
Dan HuffNov 8, 2013 8:54 AM (in response to michael.chan.0)
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