Running sum gaps

[Olek]

Hello

I have some issues with running sum function. I’m trying to sum some values over time and divide it by some dimension. However on dates, when I do not have any value for certain day and dimension item, then that value disappears completely.

As you can see in marked dates I am missing purple values in next week.

Calc field used:

runningSum(

sum(coalesce({measure},0)),

[{date} ASC],[{dimension}]

)

Do you know any workaround? The only one comes to me is to create artificial records per date per dimension, however it’s not an option for me, because of many different dimensions and amount of dates to cover. I’ve also tried to right (and full) join dimension, however it didn’t work for me.
Thanks

[Brett]

Hi @Olek and welcome to the QuickSight community!
Apologies from the community on this message not being addressed earlier, for some reason it just popped up in the ‘New’ feed!
Please see this previous post that covers a topic; it looks as though this is currently not possible:

runningSum - how to avoid NULL values

Unfortunately this is a limitation of Quick Sight. 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.

I’ll go ahead and mark this as a feature request to promote visibility to the AWS support team.

[Olek]

Hi,

I’ve tried to full join complete set of dates to our facts. However, but still problem remains AS I understand we would need to generate artificial records to combine each combination of dimensions. But it seems to be overkill and waste of precious size in SPICE.
It seems like I’m stuck with that issue.