1 Reply Latest reply on Dec 16, 2013 12:30 PM by Jean Jacques Potgieter

    Need help creating a dual axis bar chart with different labels depending on the year

    Jean Jacques Potgieter

      Hello everyone. I'm a fairly new Tableau user so this problem may be pretty simple but as of now I'm stuck.

       

      I want to create a bar chart that compares Billed hours to Non-Billed hours by quarter. I have an excel file that has a column for Hours Worked. I have another for Hours Billed. I created another column for Non-Billed Hours that subtracts the hours billed from the hours worked. I want to create a chart that has the number of hours worked on the Y axis and the date (in quarters) on the X axis. The bars should show what percentage was billed vs unbilled. I attached a quick sketch in paint of how I want the graph to look. Billable Value, Hours Value, and Non-Billable Value are another column where I took the hours and multiplied by 150. 

       

      The tricky part however, is they want a certain amount of hours subtracted from the total hours each year. So in 2012 they want the total hours - 15 and in 2013 they want the total hours -7.

       

      When I edit the labels for the 2012 values, the 2013 change too because of how I set it up. I'll attach a screenshot of how it currently looks.

      Thanks in advanced.

        • 1. Re: Need help creating a dual axis bar chart with different labels depending on the year
          Jean Jacques Potgieter

          This seems like it would be easy to do but I still can't figure it out. I tried creating a conditional label but I'm running into some problems.

           

          When I try:

          IF [Date Worked] < DATE("01/01/2013") THEN [Non-Billable Value]/Hours Worked Value] ELSEIF [Date Worked] >= DATE("01/01/2013") THEN [Non-Billable Value]/[Hours Worked Value] END

          I get really weird results, most likely because it isn't aggregating the hours. However when I try sum([Non-Billable Value])/sum([Hours Worked Value]) I get an error about aggregation mixed with non-aggregation. The only thing I could do to get rid of the error message was to try:

          IF attr([Date Worked]) < DATE("01/01/2013") THEN sum([Non-Billable Value])/sum([Hours Worked Value]) ELSEIF attr([Date Worked]) >= DATE("01/01/2013") THEN sum([Non-Billable Value])/sum([Hours Worked Value]) END

          However nothing showed up when I tried this; it was just blank. Does anyone have any suggestions?