topic Re: Rolling cumulative sum by groups of column entries in Discussions

Rolling cumulative sum by groups of column entries

dileepkr — Tue, 06 Aug 2019 21:08:43 GMT

Hi,

I am trying to create the cumulative sum column as shown in the table below.

When I use the Col cumulative sum( value) directly, it gives cumulative sum for entire value column. Can anyone please help me understand how to calculate the cumulative sum for groups of Type as shown in table below?

Name	Type	value	cumulative sum
AAA	T1	1	1
AAA	T1	10	11
AAA	T1	32	43
AAA	T1	4	47
AAA	T2	2	2
AAA	T2	3	5
AAA	T2	5	10
AAA	T2	6	16
BBB	T1	32	32
BBB	T1	34	66
BBB	T1	5	71
BBB	T1	67	138
BBB	T2	34	34
BBB	T2	32	66
BBB	T2	7	73
BBB	T2	8	81

Re: Rolling cumulative sum by groups of column entries

txnelson — Tue, 06 Aug 2019 21:13:30 GMT

Use the by capability within the Col Cumulative Sum() function.

col cumulative sum(:value, :Name, :Type);

Re: Rolling cumulative sum by groups of column entries

dileepkr — Tue, 06 Aug 2019 21:19:05 GMT

Thank you for quick and precise answer :)

Re: Rolling cumulative sum by groups of column entries

pswilliams — Wed, 14 Apr 2021 21:38:12 GMT

Hi txnelson - I'm trying to do a very similar task, however I'm missing something basic.

I have a list of projects with columns for date_start, status, description, etc.

I would like to create a graph demonstrating a cumulative total of "Active" projects, grouped by m/y. I see this as basically a running count function grouped by date. (starting date format if d/m/y and spans multiple years)

I created the m/y column for grouping, but I haven't been able to graph the count of projects organized by the m/y group. I think I'm missing something basic.

If there is a scripting solution, I will need a guide as to how to input it as I'm not experienced with JSL.

Thanks

Re: Rolling cumulative sum by groups of column entries

txnelson — Wed, 14 Apr 2021 22:09:45 GMT

Use
Tables=>Summarize
Using your m/y column as the grouping column, and nothing else as the statistics column. The result will give you a data table that can be plotted where # Rows is the count of projects for that m/y

Re: Rolling cumulative sum by groups of column entries

pswilliams — Fri, 16 Apr 2021 19:03:34 GMT

Thanks! This worked perfectly.