Handle Nulls in Running Calculations

Feature Suggestions
1

Current State: If no values are returned for a series used for a running calculation, the running calculation is returned as null, with the running calculation continuing on the next instance of data.

Desired State: Return the previous value for the running calculation when null is returned in the series

We have found this a few times when working with pivot-driven reporting (pivot tables, column charts). When visualizing a running total of sales by customer by week, not every customer has sales for each week. When these gaps occur, the running total in the visualization disappears for that week (when working with window aggregate AQL measures); Moving calculations handle these correctly, but are limited to line-style visualizations.

When a total (cross-customer) running sales total is added, the running total for customers with no sales in a given week are removed from that week’s running total. This leads to unexpected dips in the overall running total. This issue occurs on both window aggregate AQL measures and Moving calculations

Example Table:

Customer A Sales Customer A Cumulative Sales Customer B Sales Customer B Cumulative Sales Aggregate Cumulative Sales
$1000 $1000 $500 $500 $1500
$700 $1200 $1500
$1200 $2200 $900 $2100 $4300
$2000 $4200 $500 $2600 $6800

Ideally, Customer A Cumulative Sales in the second row would continue with $1000 (the previous running value) and the Aggregate Cumulative Sales output would be $2500 for that row.

2

Hi @Bob_Adams, thanks for the question!

Can you share more details about how you’re setting up the example table, what’s the dims you are using, and what’s the AQL expressions you’re using for Cumulative Sales?

I’ve tried to set up a similar pivot table on my end:

  • Countries as columns.
  • Day number as rows.
  • Total orders as value.
  • Running total on total orders as another value. (running_total(total_orders, orders.created_date))

I found that it’s working as expected. Please look at the highlighted section below:

  • On Feb 18th, 2022, Vietnam (the country) has no new orders.
  • The Total column for Feb 18th, 2022 shows the number 8 for Total orders, which is the correct sum of total orders from other countries.
  • The Total column for Feb 18th, 2022 shows the correct number 141 for Running Total, which is the previous value (133) plus the new value (8).

image
image1920×1372 135 KB

In your example table, using a similar setup, I’d expect:

Customer A Sales Customer A Cumulative Sales Customer B Sales Customer B Cumulative Sales Aggregate Cumulative Sales
$1000 $1000 $500 $500 $1500
$700 $1200 $2200
$1200 $2200 $900 $2100 $4300
$2000 $4200 $500 $2600 $6800

Please let me know if there’s any other factors that play into this.