Cumulative Sum - AGGR by dimension

[DrB1]

Data looks like this - 

Family Name	Gift Date	PledgeNum	Pledged	Individual Name	IsGivingShared	Sum(GiftDetail_Amount)	Cumulative Family Amount	Max(GiftDate)
Family1	5/10/2022	-	No	Individual1	False	$ 200.00	200	5/10/2022 0:00:00  TT
Family1	10/9/2022	-	No	Individual1	False	$ 250.00	450	10/9/2022 0:00:00  TT
Family2	3/13/2023	-	No	Individual2	False	$ 325.00	775	3/13/2023 0:00:00  TT
Family3	9/25/2022	-	No	Individual3	False	$ 314.00	1089	9/25/2022 0:00:00  TT
Family4	6/23/2022	27	Yes	Individual4	True	$ 50.00	1139	6/23/2022 0:00:00  TT
Family4	7/23/2022	27	Yes	Individual4	True	$ 50.00	1189	7/23/2022 0:00:00  TT
Family4	8/26/2022	27	Yes	Individual4	True	$ 350.00	1539	8/26/2022 0:00:00  TT
Family4	9/23/2022	27	Yes	Individual4	True	$ 300.00	1839	9/23/2022 0:00:00  TT
Family4	9/26/2022	27	Yes	Individual5	True	$ 100.00	1939	9/26/2022 0:00:00  TT
Family4	########	27	Yes	Individual5	True	$ 100.00	2039	10/25/2022 0:00:00  TT
Family4	########	27	Yes	Individual4	True	$ 200.00	2239	10/25/2022 0:00:00  TT
Family4	########	27	Yes	Individual4	True	$ 470.00	2709	11/17/2022 0:00:00  TT
Family4	########	27	Yes	Individual5	True	$ 100.00	2809	11/25/2022 0:00:00  TT
Family4	########	27	Yes	Individual4	True	$ 200.00	3009	12/15/2022 0:00:00  TT
Family4	########	27	Yes	Individual5	True	$ 100.00	3109	12/25/2022 0:00:00  TT
Family4	1/25/2023	27	Yes	Individual5	True	$ 100.00	3209	1/25/2023 0:00:00  TT
Family4	2/24/2023	27	Yes	Individual4	True	$ 200.00	3409	2/24/2023 0:00:00  TT
Family4	2/25/2023	27	Yes	Individual5	True	$ 100.00	3509	2/25/2023 0:00:00  TT

I'm using this expression for Cumulative amount in the above table:  RangeSum(Above(total sum(GiftDetail_Amount),0,rowno(total)))

What I need is to aggr the cumulative amount by Family (a dimension). I want the data to look like table below - I've tried several things but cannot get it to work. Can anyone help me?? 

Family1	5/10/2022	-	No	Individual1	False	$ 200.00	200	5/10/2022
Family1	10/9/2022	-	No	Individual1	False	$ 250.00	450	10/9/2022
Family2	3/13/2023	-	No	Individual2	False	$ 325.00	325	3/13/2023
Family3	9/25/2022	-	No	Individual3	False	$ 314.00	314	9/25/2022
Family4	6/23/2022	27	Yes	Individual4	True	$ 50.00	50	6/23/2022
Family4	7/23/2022	27	Yes	Individual4	True	$ 50.00	100	7/23/2022
Family4	8/26/2022	27	Yes	Individual4	True	$ 350.00	450	8/26/2022
Family4	9/23/2022	27	Yes	Individual4	True	$ 300.00	750	9/23/2022
Family4	9/26/2022	27	Yes	Individual5	True	$ 100.00	850	9/26/2022
Family4	10/25/2022	27	Yes	Individual5	True	$ 100.00	950	10/25/2022
Family4	10/25/2022	27	Yes	Individual4	True	$ 200.00	1150	10/25/2022
Family4	11/17/2022	27	Yes	Individual4	True	$ 470.00	1620	11/17/2022
Family4	11/25/2022	27	Yes	Individual5	True	$ 100.00	1720	11/25/2022
Family4	12/15/2022	27	Yes	Individual4	True	$ 200.00	1920	12/15/2022
Family4	12/25/2022	27	Yes	Individual5	True	$ 100.00	2020	12/25/2022
Family4	1/25/2023	27	Yes	Individual5	True	$ 100.00	2120	1/25/2023
Family4	2/24/2023	27	Yes	Individual4	True	$ 200.00	2320	2/24/2023
Family4	2/25/2023	27	Yes	Individual5	True	$ 100.00	2420	2/25/2023

[Michael_Tarallo · Accepted solution]

Hi DrB1 - I am looking at unanswered threads and came across this one.

I'll attempt to assist - RangeSum() only works with 1 dimension - if you remove the other columns and only keep FamilyName and GiftAmount  you will see an accumulation:

rangesum( above( sum(GiftDetail_Amount),1,rowno()))

BUT - let's see if we can get this working with more dimensions

In order to do this - you need to use the AGGR() function and include the dimensions you are sorting by:

=Aggr(RangeSum(Above(Sum(GiftDetail_Amount), 0, RowNo())), [Family Name],[Gift Date])

This should give you a general idea - I attached the export of the app for you

Please let me know if this helps - you can make adjustments to the sorts etc, I just copied your data from the thread and used it in the app.

[DrB1]

@Michael_Tarallo thank you for this. It does work on a small number of rows, but when I try it with the entire data set, it isn't calculating properly. I will work with it again today to see if I can determine why not. I know the expression is correct - so I'm thinking perhaps something is conflicting with the other fields in the dataset.

[DrB1]

@Michael_Tarallo  - I found it - it was the ordering as described here.  I used: =Aggr(RangeSum(Above(Sum(GiftDetail_Amount), 0, RowNo())),(Family0_Label, (Alphabetic, Ascending)),(GiftDate,(Numeric,Ascending))). Works great! Thank you for your assistance! By the way - keep making the training videos!!

[DrB1]

One correction in my expression above - "Alphabetic" should be "Text".