Limited period graph with All Time Rolling Total

marcel_luthi · 2022-12-19T16:35:44+00:00

There was an error rendering this rich post.

Currently using a Line Bar chart, to graph issue creation vs issue closure over different time periods, which also allows us to have a running total line that shows the total change.

However, we would like the total line to show the actual total for all time, so instead of starting from 0, the first month should start with the actual All Time history regardless of the Time Range that has been selected to be displayed. I'd need to do this via Beast Modes, as people can select different filters at the card/dashboard level, and also switch the date grouping as needed. I was thinking perhaps replacing the line with a beast mode that uses Windows Functions or the Fixed function would work, but haven't been able to come up with the right syntax. So the outcome should look like the red line below, basically the blue running total we already have but uplifted by the preexisting balance (outside the selected date range):

My raw data currently has 3 key columns: Milestone (to tell what happened, like created vs closed), Milestone Date (when it happened) and the Issue ID, plus additional columns that are used for filtering.

Any ideas?

Find more posts tagged with

Accepted answers

All comments

GrantSmith

You can utilize a window function to do this:

SUM(COUNT(`Issue ID`)) OVER (ORDER BY `Milestone Date`)

marcel_luthi

Hi @GrantSmith thanks for the answer. Unfortunately the Window function will only look at the items that exist in the time range, and I need to include the values preexisting dates. I could retrieve the grand total with something like:

SUM(SUM(CASE WHEN `Milestone` = 'Created' THEN  1 WHEN `Milestone` = 'Done' THEN -1 END) FIXED (FILTER NONE))

But this only gives me the current total and not for each individual period, also while using FILTER NONE allows me to ignore the Date Range constrain, it'll also nullifies any other filter applied to the card/dashboard, so if they want to know issues only corresponding to an specific repository or for specific Priorities, this won't work (I haven't found a way to do a FIXED function that just ignores the Date Range but still respect any other filter, tried using FIXED (FILTER DENY `Date Field`) but this did not work. I know I lacked context, but in case it helps the dataset looks something like:

GrantSmith

Ah, in that case you'd want to use the fixed function to be able to ignore filtering. Which date field is driving your chart? Is that the Milestone date?

In your beast mode you're missing the order by which makes it a running total. Without it it's just a grand total of your entire dataset (or partition if you have one specified)

SUM(SUM(CASE WHEN `Milestone` = 'Created' THEN  1 WHEN `Milestone` = 'Done' THEN -1 END) FIXED (ORDER BY `Milestone Date` FILTER DENY `Milestone Date`))

marcel_luthi

Thanks for the suggestions, for some reason when I try to use ORDER BY in the FIXED clause, it throws a syntax error. I tried just using the FILTER DENY applied to the same field as the Date Range, and compared it against the value of FILTER NONE when no other filters are applied, and the deny just gets me the value limited to the Date Range, while none does looks at the whole universe.

I was looking into the FIXED documentation but couldn't find any information about using order by, just a disclaimer that it cannot be used in conjunction with Window functions.

marcel_luthi

Just in case this might help others facing a similar problem, I was able to work around this by using a variable that determines the earliest date I want to show in the chart (you could do this to be a formula instead should you always want to start N months back), and changing the data we use for the X axis for the following beast mode:

CASE WHEN `Milestone Date` <= `Variable Date` THEN `Variable Date` ELSE `Milestone Date` END

This way the first entry will show the total of everything that happened until that date, and the following entries will show the changes and the rolling total will show the current value.