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.
