Forum Discussion

RoulaHabbal's avatar
RoulaHabbal
Qrew Member
2 months ago

Graduate Count per Program per Year

Hello:

I have a Graduation Data table that includes the following fields:

  • Term - Text-Multiple Choice Field

  • Program Title -Text Field

  • Count - Numeric Field

Data

2019 Spring, Science Program, 10 graduates

2019 Summer, Science Program, 20 graduates

2019 Fall, Science Program, 30 graduates

 

2019 Spring, Math Program, 5 graduates

2019 Summer, Math Program, 10 graduates

2019 Fall, Math Program, 5 graduates

 

Goal: To create a bar chart that states the Science Program had a total of 60 graduates and the Math Program had a total of 20 graduates in 2019

 

I tried different combinations of X and Y axes but could not get the bar chart the way I wanted. I figured  I would need to create a new field, Graduate Count per Program per Year Formula - Formula Numeric, but I cannot get any formula to work. I realize I cannot have two different field types in a formula.

Any help would be greatly appreciated.

 

Thank you,



------------------------------
Roula
------------------------------

5 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    2 months ago

    One solution would be to create a new field for 'year' that parses out the year from your Term value, so you have a distinct value that says 2019, 2020, 2021 etc. Then you can do a Stacked Bar Chart where you group by the Year Chat as the X value and you can split the bar based on your Program Title so it shows the itemization by program and then a grand total. 

    You could play with other chart types if you prefer, but you'll want the year field no matter what. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • RoulaHabbal's avatar
      RoulaHabbal
      Qrew Member
      2 months ago

      Thank you, Chayce. I appreciate your quick response. I am one step closer to what I am trying to accomplish.



      ------------------------------
      Roula Habbal
      ------------------------------
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    2 months ago

    To expand on Chayce's suggestion:

    You can extract the year (as he is stating) with a formula: [Term Year] = Trim(Left([Term]," "))

    Bar chart on x axis will sort by Term Year (equal values).

    Data values on y axis will be the Count, Summed.

    In series you select the Program Title.



    ------------------------------
    Mike Tamoush
    ------------------------------
    • RoulaHabbal's avatar
      RoulaHabbal
      Qrew Member
      2 months ago

      Thank you, Mike. I appreciate your detailed message. I am one step closer to my goal, and my new chart looks good, except the legend displays numbers rather than the program names. How can I display the program names instead of numbers in the legend?



      ------------------------------
      Roula Habbal
      ------------------------------
      • RoulaHabbal's avatar
        RoulaHabbal
        Qrew Member
        2 months ago

        Update - I figured it out.
        Thank you, gentlemen, for your help and time.



        ------------------------------
        Roula Habbal
        ------------------------------