Runningsum results appear blank

Question & 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!

6

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?

7

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.