Runningsum results appear blank

Question and answer
,
#1

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:

#2

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
#3

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?

#4

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
#5

Thank you so much for the explication!