-
1. Re: Colour all values above a constant
Alex Kerin Nov 29, 2012 7:26 AM (in response to Ayush Baheti)It looks like you want to color part of a bar blue, and the rest red? If not, can you mock up what it would look like?
-
2. Re: Colour all values above a constant
Ayush Baheti Nov 30, 2012 3:54 AM (in response to Alex Kerin) -
3. Re: Colour all values above a constant
Shawn Wallwork Nov 30, 2012 4:35 AM (in response to Ayush Baheti)Good Morning Ayush.
Attached is workbook that does what you're looking for. It's a dual-axis bar chart that uses this formula to produce the blue bars:
IF SUM([Sales])<400000 THEN SUM([Sales]) ELSE 400000 END
It looks like this:
Make sure you sync the axis or it won't work correctly. Also you can bring the blue to the front by re-arranging the color legend (if necessary).
--Shawn
-
Ayush.twbx.zip 1.1 MB
-
-
4. Re: Colour all values above a constant
Ayush Baheti Nov 30, 2012 4:45 AM (in response to Shawn Wallwork)Dual axis chart! Of course. Why didn't I think of it before. Thanks a lot, Shawn. Just out of curiosity, in case we wanted to have 3 colours, then what to do?
-
5. Re: Colour all values above a constant
Shawn Wallwork Nov 30, 2012 5:07 AM (in response to Ayush Baheti)-
Ayush-2.twbx.zip 1.1 MB
-
-
6. Re: Colour all values above a constant
Ayush Baheti Nov 30, 2012 5:12 AM (in response to Shawn Wallwork)What I meant was for sales itself. Say I want to divide my line into 3 segments. 0-100K Green, 100K-200K Blue, 200K+ Red. Then, what to do?
-
7. Re: Colour all values above a constant
Shawn Wallwork Nov 30, 2012 5:31 AM (in response to Ayush Baheti)It's the same idea. Use Measure Values to include a duplicate of the Sales field. You calc on the dup would look something like this:
IF SUM([Sales])>400000 AND SUM([Sales])<1000000 THEN SUM([Sales]) ELSE 1000000 END
Results in this:
See Attached.
--Shawn
-
Ayush-3.twbx.zip 1.1 MB
-
-
8. Re: Colour all values above a constant
Ayush Baheti Nov 30, 2012 5:51 AM (in response to Shawn Wallwork) -
9. Re: Colour all values above a constant
Alex Kerin Nov 30, 2012 5:56 AM (in response to Shawn Wallwork)Nice job Shawn. For some reason I was stuck in padding the data out to get two marks.
Ayush, you may want to also consider reference bands as well - http://onlinehelp.tableausoftware.com/v7.0/public/online/en-us/reflines_addbands.html
Then you could have as many colors as you like
-
10. Re: Colour all values above a constant
Ayush Baheti Nov 30, 2012 6:01 AM (in response to Alex Kerin)I know about the Reference Band feature and use it often. Just wanted a similar functionality with the bar-colour as well. Thanks for the refresh, though
-
11. Re: Colour all values above a constant
Shawn Wallwork Nov 30, 2012 6:08 AM (in response to Ayush Baheti)Ayush, you're right using Measure Values to add the third color messes up the totals. Maybe Alex will have an idea of why and how to fix this. I'm off for a bit chasing down gremlins of my own.
--Shawn
-
12. Re: Colour all values above a constant
Alex Kerin Nov 30, 2012 10:51 AM (in response to Shawn Wallwork)Using Shawn's method of stacking the bars with separate measures, we can dispense with the double axis and tweak the formulas a little. Note the order in the Measure Values Shelf is important:
-
Ayush-3.twbx.zip 1.1 MB
-
-
13. Re: Colour all values above a constant
Shawn Wallwork Nov 30, 2012 11:12 AM (in response to Alex Kerin)Yep that's it. Nice work Alex. Thx.
--Shawn
-
14. Re: Colour all values above a constant
Ayush Baheti Dec 2, 2012 10:23 PM (in response to Alex Kerin)Thank you. This, along with reference bands, will make my dashboards look really neat.