topic Re: Cumulative Sum in table chart in App Development

Cumulative Sum in table chart

Daya_Acc — Mon, 14 Oct 2024 12:01:08 GMT

Hi All,

I have data in the below table format and wanted to do the cumulative sum on the basis of channel.

I'm trying to get the below output.

Please help me to get the cumulative sum on the channel basis.

Thank you in advance!.

/Daya

Re: Cumulative Sum in table chart

diegozecchini — Mon, 14 Oct 2024 12:11:30 GMT

Hi!
to achieve a cumulative sum based on the "Channel" in Qlik, you'll need to follow a few key steps using set analysis and the RangeSum() function. Here's how you can implement this:

Ensure that your data is loaded in a format where you have the fields: Week, Channel, and Reached and create a new table chart in Qlik and add the dimensions Week and Channel.
To calculate the cumulative sum based on the Channel, you can use the RangeSum() function in combination with Above().

Use the following expression as a measure for the cumulative sum:

RangeSum(Above(TOTAL Sum(Reached), 0, RowNo(TOTAL)))

This expression does the following:

Sum(Reached) calculates the sum of the "Reached" value.
Above() is used to look at the rows above the current one.
RowNo(TOTAL) ensures that the function operates over the entire dataset (instead of just a single part of the table).
RangeSum() sums up the values from the top of the column down to the current row.

If you want the cumulative sum to reset based on each Channel, modify the expression slightly to group by the channel:

RangeSum(Above(Sum(Reached), 0, RowNo(TOTAL <Channel>)))

This ensures that the cumulative sum resets for each new channel.

The final table should display the cumulative values, grouped by Week and Channel, as you've shown in the image.

Re: Cumulative Sum in table chart

Kushal_Chawda — Mon, 14 Oct 2024 13:42:41 GMT

@Daya_Acc try below. Assuming your Week is in NUMERIC format

=sum(aggr(rangesum(above(sum(Value),0,RowNo(total))),Channel,(Week,(NUMERIC))))

if week is in TEXT format, you might need to make either in dual format so that we can sort it in aggr function. You can try below as well

=sum(aggr(rangesum(above(sum(Value),0,RowNo(total))),Channel,Week))

Re: Cumulative Sum in table chart

Daya_Acc — Wed, 16 Oct 2024 06:59:33 GMT

@diegozecchini @Kushal_Chawda,

Appreciate for your quick response. I found the required result by using below expression:

sum(aggr(RangeSum(above (total(Reached),0,RowNo())),Channel,Week))

Thank You!