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

[#1]

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.

[#2]

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.

[#3]

Jim,

Thanks for responding. I've got something working to that effect already.

My problem is a bit different. So using the Gantt example, I was wanting to vary say complete fill color as a function of another column. Essentially I have one column defining the size of the bars as a function of time and another defining the color. So the dark blue for complete would be driven by other data - could be something like staffing levels - where Requirements might be Green, Design might be Yellow and Red for the Complete and Incomplete sections, etc. That's the problem I'm having.

Thanks.

[#4]

The illustration I gave you was showing how, using the Gantt Addin the final data table would have to approximatly look like to generate a chart of the complexity you want.  If you back that up, to what the initial table would look like, I can envision a data table with columns indicating the specifications you want, and then for the jsl to take that input, and to generate behind the scenes a data table and a graph builder script to generate your output.

I don't believe that there is a solution where you would have a data table coupled with a static Graph Builder script to produce your desired results.  It will take some jsl to interpret your input and to generate a new data table and a Graph Builder script to meet your needs.

[#5]

Thanks for your help Jim.

I'll see if I can JSL some stuff to make it work.