
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Cumulative Sum - AGGR by dimension
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 |
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Mike Tarallo
Qlik


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Mike Tarallo
Qlik

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
One correction in my expression above - "Alphabetic" should be "Text".
