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

ifelse({indicator=1},{balance},0)

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.

2 Likes

Thank you so much for the explication!