Is there a way to vary bar chart fill colors as a function of another variable?

pbrett

Greetings.

I am trying to create a Gantt chart where a program may have different fill colors within segments depending on whatever criteria. I've made up some dummy data just for discussion purposes and attached a visual example. Say we have three projects with variable staffing needs over time (different times per project) and I'd like for the bar fill colors to represent the low, med, or high staffing information.

Header 1	Time 1	Staffing 1	Time 2	Staffing 2	Time 3	Staffing 3
Project 1	Jan-Feb	Low	Mar-Aug	High	Sept-Dec	Med
Project 2	Jan-June	Med	July-Oct	Low	Nov-Dec	Med
Project 3	Jan-Apr	Med	May-July	Med	July-Dec	High

I found and played with the Gantt chart addin but that isn't suitable for my needs, though I did use it as a launching point in using Graph Builder to make something for me. I've got it working so I can use dates to create different bars for a particular project based on the time. But right now the fill color is a function of time period so all of Time 1 is the same, all of Time 2 is the same, and all of Time 3 is the same.

Can I use conditional statements in the Legend Model properties for Fill Color? With 1,2,3 being Low, Med, High respectively and Model reference 5 being linked to Time 1 bounds, essentially something like...

Legend Model(                5,                Level Name( 0, "Time 1" ),                Properties( 0, if (Staffing 1 = 1,{Fill Color( 57 )}, Staffing 1 = 2, {Fill Color( 47)}, Staffing 1 = 3, {Fill Color( 37)},  )            )

Ideally I'd also like to add markers for milestones in as well but I don't know if that is feasible. There are other tools that can probably do this easier but someone asked about JMP after showing them an Excel example so here I am.

Thanks.

Example.png

txnelson

The simple answer is Yes:

 

However, it makes the Graph Builder and the data table more complex.  If using the Gantt Chart Addin as the structures model, you would have to add a new column to contain the new end data for each different type of fill pattern you want.  And then the Graph Builder chart would have to be modified to add in a new Bar with the range set for the correct starting column to the new column in the data table that was created for the new category.  Below is the code for the graph builder with just one new fill patter type.  I changed the fill pattern, but it could have just as easily have been a solid pattern with just a different color.

Graph Builder(
       Size( 907, 626 ), 
       Show Control Panel( 0 ),
       Variables(
              X( :Start Date ),
              X( :donedate, Position( 1 ) ),
              X( :End Date, Position( 1 ) ),
              X( :Extended Date, Position( 1 ) ), 
              Y( :Task, Order By( :Start Date, Descending, Order Statistic( "Mean" ) ) )
       ),
       Elements(
              Bar( X( 1 ), X( 3 ), Y, Legend( 32 ), Bar Style( "Range" ) ),
              Bar( X( 1 ), X( 2 ), Y, Legend( 20 ), Bar Style( "Range" ) ),
              Bar( X( 1 ), X( 4 ), Y, Legend( 44 ), Bar Style( "Range" ) )
       ), 
       SendToReport( 
              Dispatch(
                     {},
                     "Start Date",
                     ScaleBox,
                     {Min( 3442521600 ), Max( 3487017600 ), Interval( "Month" ), Inc( 1 ),
                     Minor Ticks( 0 ), Label Row( Label Orientation( "Angled" ) )}
              ),
              Dispatch(
                     {},
                     "Task", 
                     ScaleBox, 
                     {Label Row( 
                           {Show Major Ticks( 0 ), Set Font( "Tahoma" ), Set Font Size( 11 )} 
                     )}
              ),
              Dispatch(
                     {}, 
                     "400", 
                     ScaleBox,
                     {Legend Model(
                           32,
                           Level Name( 0, "Incomplete" ),
                           Properties( 0, {Fill Color( 77 )} )
                     ), Legend Model(
                           20,
                           Level Name( 0, "Complete" ),
                           Properties( 0, {Fill Color( 53 )} )
                     ), Legend Model(
                           44,
                           Properties( 0, {Fill Color( 74 ), Fill Pattern( "hatch heavy" )} )
                     )}
              ),
              Dispatch( {}, "graph title", TextEditBox, {Set Text( "Gantt Chart" )} ),
              Dispatch( {}, "X title", TextEditBox, {Set Text( "Date" )} ),
              Dispatch( {}, "Y title", TextEditBox, {Set Text( "Tasks" )} )
       )
);

  And this is the data table with the modification I had to make to allow for the new bar category.  Notice the new column called Extended Date.  In the model I am proposing, additional columns like Extended Date would have to be added.