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
-
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
-
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
-
Thanks a lot jmyzk.😀
I will try it out.
-
Glad I could help! Upvote for more!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!