- You use start and due dates to manage high-level timelines for your projects
- You would like a visual representation of those dates at the project level
- You would like to challenge yourself with a more advanced use case
- I'm going to start with a standard Pivot and add Project Link, Project Name and Project Start and Due dates in Day grouping.
Next, we'll add our Custom Timeline, which will graphically show when each project is active. Our goal is to have the months when the project is active show in blue, and the months the project is not active show in gray.
We'll begin with the start of our timeline, which will be January of the current year (or whichever month is the start of your fiscal year).
- Add a new value, Count of Project ID. Then edit the formula.
- Now's where the fun begins. Surround the entire expression in ( ), then add two more dimensions/fields with commas as shown in the screenshot. This is called a formula filter, we're filtering to show only those projects active in January.
Hint: Always select the dimension from the data browser rather than typing directly in the formula.
- To set the formula filter:
- Right click on Project Start Date, select Filter, then choose the following filter: Calendar - Earliest Date to end of January. Rename field to read "Project Start Date is on or before Jan (year)"
- Right click on Project Due Date, select Filter, then choose the following filter: Calendar - start of January to Latest Date. Rename field to read "Project Due Date is on or after Jan (year)"
- Finally, as a required step, add an unfiltered COUNT([Project ID]) - 1 + to the top line of the formula. This prevents the formula filter from filtering the rest of the widget.
- Now save the formula (blue button on the BOTTOM), and you should see 1s on projects that are active in January, and 0s on projects that aren't. Rename your formula and set your conditional formatting and your first timeline point is done!
- To make your next timeline points, simply duplicate your first point and change the formula filters.
Sam Alter Wrike Team member Become a Wrike expert with Wrike Discover