Dashboarding help

Options
Brian Erdelyi
Brian Erdelyi ✭✭✭✭
edited 02/05/20 in Formulas and Functions

Im trying to use a chart widget on my dashboard to display the following:


The Client Name and Deliverable Status of any job (any row) in my "Job Active" sheet that is catagorized as "Plan Development". "Plan Development" is a dropdown field choice in the "Job Type" column


Client Name and Deliverable Status are also columns in my sheet.


Not every job that is catagorized as "Plan Development" has a Deliverable Status assigned. Before a job reaches the first deliverable the associated Deliverable Status cell cell is blank.


My deliverable status column dropdown containns the following choices: 1-25%, 2-50%, 3-75%, and 4-100%


My vision is that if the Job Type is "Plan Development" but there is no deliverable status assigned then the client name will show up in the chart with no horizontal bar. When it reaches the first deliverable status then the horizontal bar will extend to 25%, When it reaches the second deliverable staus then the bar will extend to 50%, When it reaches the third deliverable staus then the bar will extend to 75%, and When it reaches the fourth deliverable staus it will drop off the chart.


I use another sheet that I call "Consultant Dashboard Data". This is where I collect the data that drives my other widgets. This is where I would like to build these formulas.


I hope I am describing this in a way that makes sense. Any help is much appreciated. I have been struggling with this for a couple days and cant make anything work.


Thank you,

Brian

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi Brian,

    It would actually be a lot simpler/faster to do this via a Report... are you open to that, or do the calculations have to happen in your Dashboard Data sheet?

    To to this in a Report, you just need to set up the criteria to pull the rows where the Job Type is "Plan Development" and the Deliverable Status is Blank, 0.25, 0.5, or 0.75. Leave off the 100% because then the clients at 100% will drop off the chart.


    When you create this report, try to only include the relevant information - the client name and the percentages. If you have a different Primary column this will be in the report as well. Move that primary column to not be the first one, and have the Client Name be the first column (this will be relevant when choosing the column as a label, in the chart widget)

    Add a Chart Widget and find the Report. You won't be able to select the data in a Report, the widget will grab the entire thing. This is why it's important to ensure your criteria is correct and that there is no additional data.

    Then you can customize your chart how you want!

    Let me know if this will work for you.

    Cheers,

    Genevieve

  • Brian Erdelyi
    Brian Erdelyi ✭✭✭✭
    Options

    That is a great idea. Thank you. I have been out with the flu but I am going to try this tonight. Thank you so much for your input!!!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    No problem at all! Let me know if I can clarify anything further. 🙂

  • Brian Erdelyi
    Brian Erdelyi ✭✭✭✭
    Options

    So... I had to make a couple minor tweeks since my percentages were listed as alpha-numeric rather than numeric values but after adding a new column to address that it worked perfect. Thank you for your help!!!

    B

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    So glad to hear it worked for you! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!