Hi all,
I’m currently utilizing runningSum and struggling with situation when aggregating data by months as in below example. is there any way to roll up the “20” for May even if no new items are not added in May yet? I’d like to avoid situation when NULLs are reflecting as in this example:
here’s the formula i’ve generated:
runningSum
(
distinct_count({items}),
[truncDate(“MM”,{date}) ASC]
)
Unfortunately this is a limitation of QuickSight.
The only way around it is to do some SQL work and make like a calendar, where you have a row for everyday and then join your data onto that.
Can I just point out to anyone from the QuickSight team just how badly this cripples the running* functions… surely the whole point of these is NOT to have to mess around in SQL to get a simple consistent running total.
Using these with a date range is such an obvious use case and you can’t expect every date bucket in that range not to have some sparse coverage especially if you are further segmenting it. Think first couple of days of the month when something hasn’t happened yet.
I totally get the difficulty of working with sparse data sets behind the scenes but not having to resort to JOINing in a full calendar date range into your data each time just to get a simple running total analysis would be such a huge win for this tool. When I’m having to do that I might as well just go and do the totting up in SQL anyway.
hi, this is not QS only issue, this is industrial common designed behavior because essentially null+numeric=null. Otherwise, it requires some special handling of null in the data source such as replacing null with 0. If you have a complete date time column, you can add a calculated field to do that, if not, then you need to join with a datetime lookup table to fill in all missing data.
If you are aware of any good workaround provided by other BI tool, please feel free to share here, we are happy to look into some out-of box support if possible. Thanks!