Need help with Project slippage chart

Hi all.

Does anyone know how to create a project slippage chart (I am sorry, if it is not the correct name) like below or similar in Smartsheet?

I believe this require the Actual Project/Task close details as well. My sheet has only Start Date, and End date columns

Kindly let me know if you are not able to view the attachment.

Thanks in advance.

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @Sonnie

    First, you can set the Baseline to check the variance between the plan and the actual schedule. (Bottom image)

    Then, get the Slippage status and rate with the following formulas;

    [Slippage Status] =IF(Variance@row > 0, "Ahead", IF(Variance@row = 0, "On Time", "Behind"))

    [Baseline Duration] =[Baseline Finish]@row - [Baseline Start]@row

    [Slippage Rate] =Variance@row / [Baseline Duration]@row

    Create a sheet like the one on the upper right to prepare chart data.

    [Slippage Rate] =AVG(COLLECT({Project slippage chart: Data : Slippage Rate}, {Project slippage chart: Data: Slippage Status}, Status@row))

    [Status & Rage ]=Status@row + ": " + INT([Slippage Rate]@row * 100 + 0.5) + "%" (Used as the series labels of the chart)

    (Create column for each statu to change the chart's color}

    [Ahead] =IF([Slippage Rate]@row > 0, COUNTIF({Project slippage chart: Data: Slippage Status}, Status@row))

    [On Time] =IF([Slippage Rate]@row = 0, COUNTIF({Project slippage chart: Data: Slippage Status}, Status@row))

    [Behind] =IF([Slippage Rate]@row < 0, -COUNTIF({Project slippage chart: Data: Slippage Status}, Status@row))

    Finally, show the date on the dashboard as a chart.

    https://app.smartsheet.com/b/publish?EQBCT=70f5a5db6e814c97af3f2bdb6ff3019f

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @Sonnie

    First, you can set the Baseline to check the variance between the plan and the actual schedule. (Bottom image)

    Then, get the Slippage status and rate with the following formulas;

    [Slippage Status] =IF(Variance@row > 0, "Ahead", IF(Variance@row = 0, "On Time", "Behind"))

    [Baseline Duration] =[Baseline Finish]@row - [Baseline Start]@row

    [Slippage Rate] =Variance@row / [Baseline Duration]@row

    Create a sheet like the one on the upper right to prepare chart data.

    [Slippage Rate] =AVG(COLLECT({Project slippage chart: Data : Slippage Rate}, {Project slippage chart: Data: Slippage Status}, Status@row))

    [Status & Rage ]=Status@row + ": " + INT([Slippage Rate]@row * 100 + 0.5) + "%" (Used as the series labels of the chart)

    (Create column for each statu to change the chart's color}

    [Ahead] =IF([Slippage Rate]@row > 0, COUNTIF({Project slippage chart: Data: Slippage Status}, Status@row))

    [On Time] =IF([Slippage Rate]@row = 0, COUNTIF({Project slippage chart: Data: Slippage Status}, Status@row))

    [Behind] =IF([Slippage Rate]@row < 0, -COUNTIF({Project slippage chart: Data: Slippage Status}, Status@row))

    Finally, show the date on the dashboard as a chart.

    https://app.smartsheet.com/b/publish?EQBCT=70f5a5db6e814c97af3f2bdb6ff3019f

  • Sonnie
    Sonnie ✭✭

    Thanks a lot jmyzk.😀

    I will try it out.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    @Sonnie

    Glad I could help! Upvote for more!😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!