Runningsum results appear blank

Hi QS savvys, I used the runningsum function to calculate the sum of balance

  • with a condition which contains the numeric value 0 and 1,
  • the sort order field is a numeric variable (no blanks),
  • the partition field is a date variable (no blanks).

The function is like this:
runningSum(sumIf({balancel}, {indicator}=1), [months ASC], [{date}])

Right now, the results from this function are blanks if the condition=0, but I want the runningsum to be 0 instead. Is there a way to do this?

Very much appreciated :slight_smile:

What if you created a calculated field


When you could write

runningSum(sum({calculated field}), [months ASC], [{date}])

1 Like

I tried and this works perfectly! Thanks you so much :smiley: Do you know the reason why putting sumif in the runningSum function causes blanks?

When the condition in sumIf is not met, Quicksight treats the row as not being there, not as zero. If you are adding things together, this is not a big deal, but it would be a big deal if you were using avgIf. So if all of the if conditions were false, Quicksight would have seen no entries. I know from other work that when Quicksight sees no entries, it displays a blank as opposed to a zero.

Here is an example where this would make more sense. Suppose you were displaying test scores, you would want to know the difference between someone who did not take a test and had no score, and someone who took a test and had a zero.

In any case, the calculated field I suggested turned the non-fields into zeros. This is fine for use in a sumIf.


Thank you so much for the explication!

Hi Andy, thank you for the workaround and the explanation. I am seeing the same issue. but my calculated field {numerator} ({balance} from above question) is a distinct_countIf({engagement_id}, condition1 AND condition2 …).
I did the ifelse, that works fine. named this field {numerator_c}
but when I try to do sum({numerator_c}), I get error:
“Nesting of aggregate functions like runningSum and RUNNINGSUM(SUM(CASE WHEN COUNT(DISTINCT CASE WHEN condition1 AND condition2 … is not allowed”

Thoughts on how to resolve this?

Here’s how I solved my issue:
1/ created a list of engagements based on condition using if-else logic in DataPrep. this contained duplicates. published dataset.
2/ Added a calculated field on UI using distinct_count(), partitioned over date and teams.
3/ Added a calculated field on UI for YTD using runningSum() on 2/ ; ordered by date , partitioned over teams.

Make sure to use the same partitions you want to show on the dashboard, otherwise there would be double counting. Thank you Andy for your help.