i have an aql measure to calculate running_total by hour in a chart. as we final work trickles in, there are some hours without volume. In these hours, the running total metric is null, but the next hour picks up the total and adds to it properly, making for a jagged chart.
Hi @stonematt,
The issue you’re encountering is a known one. It occurs because the running total is being calculated correctly, but the pivoting process introduces empty cells (not true null values) where data is missing.
For example, if you have a table with order statuses and order dates, running totals are only calculated for the statuses present on a given day in table form (it can’t fill missing statuses because there are no rows for them).
When the data is pivoted by status, any missing statuses for a particular day result in empty cells (visual only) being added to the pivoted table.
I understand that this behavior can be confusing, and I’m looking into address it in the near-ish term.
But, in the meantime, a potential workaround is to adjust your data model to fill in the missing rows before pivoting. Below is an example of how you can achieve this. Please adapt it to your specific case:
Model orders {
type: 'query'
query: @sql
select id, status, created_at, user_id, discount, delivery_attempts
from ecommerce.orders
union all
select null, b.status, a.created_at, null, null, null
from (
select distinct
date_trunc('day', orders.created_at AT TIME ZONE 'Asia/Singapore') created_at
from
ecommerce.orders
) a
cross join (
select distinct
status
from
ecommerce.orders
) b
;;
label: 'Orders'
}
By filling in these missing rows, the value is present before the pivot operation and no longer introduce empty cells.
If you have any questions or need further assistance with implementing this solution, please don’t hesitate to let me know.
I don’t think my users will understand that this is a need if they build their own running total viz on a personal dashboard. I’ve been preferring AQL everywhere I can, so this kind of SQL work around feels kind of messy and hard to maintain.
I would upvote a fix to this.