I’m trying to create a chart in a report that looks like the following sketch drawing (please ignore the blue line as it’s not relevant to this question):
The data structure I’m trying to create it from is as follows:
Epics (as in “sub-projects”)
Epics have two computed fields for a total estimation of duration (upper/lower bound). They don’t carry a useful time value.
Progress Estimations
Progress Estimations estimate (or “guess”) the progress (0-100%) of an Epic. The database contains a timestamp, the relation to the epic (one epic has many estimations), and two (computed) values for progress, again upper and lower bounds. Note that a given set of estimations doesn’t need to cover all epics. If some epics don’t have an estimation, that is perfectly fine (counts as 0%)
Now back to the sketch: The purple and yellow lines chart the estimations at different points in time (x-axis is time), and they work nicely.
However, as these estimations have a natural upper bound (“100% completion”) I would like to add that line as an annotation to the chart. So, for a given set of epics (from the filtered source data) I would like to display the sum of all values as the 100%-annotation on the purple and yellow lines. However, the value itself does not appear in the chart (since the x-axis is time, and the epic doesn’t carry a useful time value).
I don’t seem to be able to calculate a value in an annotation that isn’t part of the actual chart. Is there a way around this? Or a different way to go about this?
I’ve compiled a little sample space to hopefully make things clearer:
In the report, I have graphed “Progress” over time. What is missing in the report is the upper bound – speaking in pseudo-code, the SUM(Task.[Time estimated]) as a horizontal line (“annotations”), meaning, the amount of time that should have been spent if all Task items had a progress of 100%. I cannot add this annotation to the existing line, because the field [Time estimated] is not available there. And I cannot add SUM(Task.[Time estimated]) as a new data row, because the Task entity has no date and thus nowhere for the value to be graphed on the timeline…
You’ve graphed the upper bound for every item separately. (You probably added a lookup to Task.Time estimated on the Progress database, right?)
What I need is the upper bound for the sum of all Tasks (!), which might be different from the sum of all Progress entities at 100% – in my example, Lorem and Ipsum have no progress attached, yet should be counted in the total sum.
However, your approach led me to another idea: I could either add an auto-linking relation from progress to every Task, and calculate the sum over this in a formula, or I could sum up all Task entities in an automation on the progress database. I will try this and report back.
So you wanted a single line to represent total progress over all tasks, and a horizontal line to represent the max value of this calculation?
In other words, no separation by task…?
Not the actual MAX() value, but the theoretical max value. This theoretical maximum being: all existing tasks having a progress of 100% attached.
Starting from the automation idea, I’ve managed to have the graph display what I want. See the updated template for how I did it, and what I wanted to achieve. It’s a bit hacky (graph color = #FFFFFF, storing calculation results in a writable number field), but it works.
I have a suggestion for a solution which will avoid the need for an automation:
create a single-select field in the Task db, with a single option (= default for all Tasks) and then use a formula to calculate the theoretical max value.
See here.
This select field should be hidden everywhere (so no-one de-selects the default option) but if you wanted, you could even make the single-select field read-only (via API) so that users can’t ever mess with it.
