Sum of 12 months of data in a visualization that shows less than 12 months

Question & Answer
,
1

I’m working with a dataset that shows energy usage for buildings, and trying to figure out how to display an energy intensity calculation as a line over the top of a bar chart of usage, but the the time windows aren’t necessarily the same and I’m struggling to figure out how to do this.

I’m using a stacked bar combo chart, with energy usage shown in the bars using the kBtu field and color-coded by the Commodity field. The whole chart is then grouped by Building, and filtered to a time window controlled by two parameters, startDate and endDate. The result is a bar chart showing the aggregated energy usage by building for whatever the selected time window is (default is 12 months).

The Energy Usage Intensity (EUI) line I want to overlay on that is a rolling 12-month number, calculated by adding up the last 12 months (relative to endDate) of energy usage and dividing by the area of the building in square feet. Here’s the formula I’m using for that:

windowSum(
    sum({kBtu}),
    [{Building} ASC],
    11,
    0
)
/ max({Area})

This works fine as long as the startDate and endDate parameters stay at their default settings of 12 months apart. However, if I change one of those dates so that the window between them is less than 12 months, the EUI calculation no longer works correctly, because the date filter is applied before it gets a chance to look at the data. So if I change it to look at 8 months (shown in my screenshot below), it only sums up 8 months of energy usage before dividing by the building area, and the numbers turn out wrong.

Example visualizations
Example visualizations2512×1746 340 KB

Is there a way for me to perform this calculation pre-filter so that even if I’m displaying less than 12 months on the chart, the calculation still looks back that far? The windowSum function doesn’t offer a PRE_FILTER option, and I tried the “date hider” trick here but because I’m using Building on the x-axis instead of a date it just complains about missing a table calculation field.

1 Like
2

Hello @jnorion, my thought on this would be to use a calculated field to filter the data rather than filtering the visual directly by the start and end date parameters.

Stop filtering the visual and instead run a calculation on the kBtu field before adding it into the EUI calculation:

kBtu filtered = ifelse({date field} <= ${EndDate} AND {date field} >= addDateTime(-12, 'MM', ${EndDate}), {kBtu}, NULL)

Then you can use kBtu filtered in the EUI calculation:

windowSum(
    sum({kBtu filtered}),
    [{Building} ASC],
    11,
    0
)
/ max({Area})

Now the visual will never be impacted by the user selection for StartDate and always show 12 months worth of data. That will be the best way to accomplish this!

3

@DylanM Oh cool! That didn’t work exactly as you wrote it, because I need to be able to change the time window the bars display rather than having that static as well, but it did get me to the solution. What I did was create a new filtered kBtu field with variable dates:

ifelse(
    Date <= ${EndDate} AND Date >= ${StartDate}, 
    kBtu, 
    NULL
)

… and then changed the EUI field to use hard-coded dates:

sumIf(
    kBtu,
    Date <= ${EndDate} AND Date >= addDateTime(-12, 'MM', ${EndDate})
)
/ max(Area)

Then I can plot them as bars and lines on the combo chart without any filters at all. When I change the start or end date with the controls at the top, the time window on the bars changes, but the line stays with the 12 month sum.

Thanks for the help!

2 Likes
4

Hello @jnorion, thank you for following up! I am glad we were able to work towards the desired result. I’ll mark your response as the solution.

1 Like