Multirow Formula Question
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I am trying to do a calculation where I take the number of records closed on a particular day and subtract it from the total remaining based on the amount of records closed the day prior. Rather than get the number total closed over time which is what I'm coming up with now I want to get the total that remains. I cannot figure out how to write a multi-row formula to do that, as I need to get first total remaining number by subtracting the amounts in the first row and then get the following total remaining by subtracting the count on the current row from the total remaining on current row -1. See below for what I am currently getting vs what is desired and a screenshot of the formula I currently have. If there is a better way of getting the desired results than to use a multi-row formula I am also open to that.
Completed Date | Count | Original Count | Total Remaining | Desired Result |
[Null] | 5 | 118 | -5 | 113 |
2023-03-15 | 1 | 118 | -6 | 112 |
2023-07-17 | 1 | 118 | -7 | 111 |
2023-08-04 | 1 | 118 | -8 | 110 |
2023-09-11 | 2 | 118 | -10 | 108 |
2023-09-18 | 4 | 118 | -14 | 104 |
Solved! Go to Solution.
- Labels:
- Developer
- Developer Tools
- Help
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I used the Multi-Row tool and created a new field [My Result] with this formula:
IF IsNull([Completed Date]) THEN [Original Count] - [Count]
ELSE [Row-1:My Result] - [Count]
ENDIF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This should work for you
if isnull([Row-1:NewTotal]) then [Original Count] - [Count] else [Row-1:NewTotal] - [Count] endif
If the NewTotal is Null, its just original count - count to start it off. After that, you are just subtracting the next count from the NewTotal that was calculated on the previous row.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That worked perfectly for me, thank you for the help.
![](/skins/images/03E14B015E5210679CFB65D9FFC7B381/responsive_peak/images/icon_anonymous_message.png)