CRM Analytics: Build Cumulative Percentage Chart

Use case: show the percentage of runners who finish the 5 km run in less than 35 minutes, less than 40 minutes, less than 45 minutes, and 45 minutes or above.

Here are the steps

1. Create a Compare Table

2. Clone "Count of Rows" column

3. Edit cloned "Count of Rows"

4. Select "Running Total" from f(x), you can add a header too

5. Switch to Query mode to calculate the percentage

Here is the original query

1. q = load "run_result";
2. q = group q by 'Bucket';
3. q = foreach q generate q.'Bucket' as 'Bucket', count(q) as 'A';
4. q = group q by 'Bucket';
5. q = foreach q generate 'Bucket', first(A) as 'A', sum(sum(A)) over ([..0] partition by all order by ('Bucket')) as 'B';
6. q = order q by 'Bucket' asc;
7. q = limit q 2000;

Update row #5 to

q = foreach q generate 'Bucket', first(A) as 'A', sum(sum(A)) over ([..0] partition by all order by 'Bucket') as 'B', sum(sum(A)) over ([..0] partition by all order by 'Bucket')/sum(sum(A)) over([..] partition by 'all') as 'cumulative (%)';

6. Format "cumulative (%)" to Percent or Custom

7. Switch the chart mode and select line chart

8. Hide A and B from Y-Axis and done

With this chart, we can easily understand the percentage of runners who complete the run in less than 35 minutes, less than 40 minutes, less than 45 minutes, and 45 minutes or above.