
1. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Jonathan Drummey Oct 26, 2012 11:30 AM (in response to Robert Bell)See the attached, using the Superstore Sales data. I used a basic bar chart to start. I used the Quick Table calculation to do a YoY growth for sales, then edited the calculation to create a new measure (that will be used for the color and label). I then created a calculated field for the KPI shape that is IF [YoY % Change] > 0 THEN "Up" ELSE "Down" END. Finally, we have to give Tableau a measure to plot the shapes on a secondary axis. What I did was create a WINDOW_MAX(SUM([Sales]))*1.2 calculated field, and used that for the measure. This will return the same result for every row (i.e. at the same height), and we can synchronize the axes with the original measure, change the Mark Type for that measure to Shape, and reset the original bars after Tableau made them shapes to. After that, it was just some formatting of the KPI, color, and labels.
Jonathan

shape on bar chart.twbx.zip 1.1 MB


2. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Robert Bell Oct 26, 2012 12:05 PM (in response to Jonathan Drummey)Thanks Jonathan, that's just what I wanted! I'll work through what you've done making sure that I understand it fully and maybe respond with the odd question, if that's ok.
Once again, heartfelt thanks
Bob

3. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Jonathan Drummey Oct 26, 2012 12:17 PM (in response to Robert Bell)You're welcome!

4. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Alex Kerin Oct 26, 2012 1:13 PM (in response to Jonathan Drummey)Really elegant Jonathan!

5. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Jonathan Drummey Oct 26, 2012 1:41 PM (in response to Alex Kerin)Thanks! All that work I did on conditional formatting this past spring has really sunk into muscle memory at this point.

6. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Robert Bell Oct 26, 2012 2:01 PM (in response to Jonathan Drummey)Hi Jonathan
I think this is one of the best things I've learnt about in representing our YOY data. Thanks so much.
Please see below. By accident, I scrapped the dual axis, and I like the fact that the arrows sit underneath the bars. But, do you know how I can a) get the arrows in the arrows to sit a uniform distance from the bars above and b) have the colour range of the arrows reflect the full available range.
Or is this a consequence of using a drill down?

7. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Jonathan Drummey Oct 28, 2012 3:35 AM (in response to Robert Bell)Hi Bob,
When you're sending out questions like this, it's more helpful to a) post a screenshot that includes the Rows & Columns Shelves, and the Marks Card (along with Pages Shelf and Filters Shelf), or  even better  b) a packaged workbook with some sample data. Based on your screenshot, I'm guessing there's is a 2nd green pill on the Rows Shelf, I don't know what it is, but it's clearly returning different values, and since each line of triangles seems to have the same Yposition I can guess that the calc is probably returning the same value for all the columns within each row.
This can be a consequence of using Tableau's builtin drilldown, but I can't say for sure without seeing more detail. Since the YoY calculation is a table calculation, the calc uses the addressing you specify in the view, and any additional discrete dimensions (or discrete measures that have Ignore in Table Calculations turned off) that are added to the view are added to the partitioning for the calc by default, so the calc is now restarting more often and returning different results.
There are a couple of general methods for workarounds that I know of, both use a parameter to choose the drilldown instead of using the builtin drill down. One is to use the parameter to swap out worksheets on a dashboard, the other is to use the parameter to swap out pills in the view. Generally, the first option gives you the most control over the layout, see this for an example: http://community.tableau.com/thread/115878.
Jonathan

8. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Robert Bell Oct 31, 2012 10:34 AM (in response to Jonathan Drummey)Thanks again for this, Jonathan.
I'll be sure to include a screen shot of the shelves and marks cards when I post further questions. (or a twbx).
I'm still working through the material you gave me which has been most helpful. I did end up using your solution for the up/down arrows using dual synchronised axes, and selecting independent axis for each cell  which seemed to make all the difference.
One other thing which you made me aware of was that you can save a quick table calculation as a measure, like any other. I hadn't realised this.
Early days, but I feel I'm getting somewhere now.
All the best
Bob

9. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Michael Vu Jun 24, 2013 7:55 AM (in response to Jonathan Drummey)Hi Jonathan
Thanks for explanation on how to create up & down arrows based on calculated fields. This was extremely helpful. As you suggested I created a WINDOW_MAX(SUM([Sales]))*1.2 calculated field to plot the arrows.When I add a grand total column (Analysis>Total>Show Row Grand Total) to the chart, the total arrow is not aligned with the other arrows. Any thoughts on how we can get the total next to the other arrows?

10. Re: Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Jonathan Drummey Jun 26, 2013 7:44 AM (in response to Michael Vu)Hi Michael,
Tableau computes grand totals as a separate computation at a higher level of aggregation, so it's a bit tricky to do this. I set it up in the attached, though didn't implement all the YoY KPIs because they don't make sense inside the grand total.
Jonathan

11. Re: Re: Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Alex Dole Dec 16, 2013 8:25 AM (in response to Jonathan Drummey)Hi Jonathan,
Thank you for sharing your conditional formatting workbooks. They've been very helpful. I was able to apply one of your methods to a workbook of mine. I've recently encountered a challenge which I thought you might know how to overcome. In the attached workbook, the up and down arrows are tied to the number of people in a certain category increasing or decreasing. There are cases in my workbook where the number of people will increase but their percentage of the overall population will stay the same or decrease (or the inverse). Do you know if there is a way to have an arrow tied to each case? In the workbook, I would want a green arrow in 2011 next to the number of males, but an arrow showing no change next to the percentage of the population. Currently the arrows adjust for both the number and percentage based on my calculation. Appreciate any help you could provide.
Thank you,
Alex

ArrowsExample.twbx 38.1 KB


12. Re: Re: Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
Jonathan Drummey Jan 15, 2014 12:32 PM (in response to Alex Dole)Hi Alex, are you still looking for help on this?

13. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
ericbelen0 May 12, 2015 11:07 AM (in response to Robert Bell)Thread resurrected!
Thank you Jonathan for this very informative post. Im now doing the same but for multiple dimensions.
Im doing a comparison only between 2 years, but across different 8 dimensions of (e.g. Revenue, Cost, Profit, Utilization, etc). I tried recreating your example but it appears I have to do 8 separate calculations and unfortunately it wont let me do more than 1 mark for shapes. Can you recreate an example even with just 34 dimensions? I can learn it from there.
Thank you!

14. Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?
abdul kareem Jan 13, 2016 6:12 AM (in response to Robert Bell)Hi Jonathan,
If Zero comes then what is the logic.