Forum Discussion
Running Total, Not Date Based
Hello,
I have a table that I'm trying to get a running total on each row for the previous 6 rows.
For example, for the TermAxis 0 row (21EW6), I want to sum the In-term leads for the rows with termaxis = 0, -1, -2, -3, -4 and -5
I have a field looking for the min term axis and I'm trying to sum where term axis >= MinTermAxis, but that doesn't seem to work. for me.
Any ideas on how I might go about this?
.
9 Replies
Here is some sample data in a copy/paste format if needed!
AcademicLevelCode Term TermAxis MinTermAxis Sum In-term Leads GR 17TW4 -20 -24 23067 GR 17TW5 -19 -23 22339 GR 17TW1 -18 -22 23323 GR 17TW2 -17 -21 20277 GR 18TW3 -16 -20 23781 GR 18TW4 -15 -19 21107 GR 18TW5 -14 -18 26371 GR 18TW1 -13 -17 23420 GR 18TW2 -12 -16 20384 GR 19TW3 -11 -15 25985 GR 19TW4 -10 -14 24766 GR 19TW5 -9 -13 23288 GR 19TW1 -8 -12 22067 GR 20TW3 -7 -11 23991 GR 20TW4 -6 -10 23145 GR 20TW5 -5 -9 32189 GR 20TW1 -4 -8 35398 GR 20TW2 -3 -7 24983 GR 21TW3 -2 -6 34249 GR 21TW4 -1 -5 26249 GR 21TW5 0 -4 28968 UG 17EW6 -24 -29 44849 UG 17EW1 -23 -28 54718 UG 17EW2 -22 -27 42965 UG 18EW3 -21 -26 55702 UG 18EW4 -20 -25 61369 UG 18EW5 -19 -24 57039 UG 18EW6 -18 -23 62101 UG 18EW1 -17 -22 80622 UG 18EW2 -16 -21 57543 UG 19EW3 -15 -20 70901 UG 19EW4 -14 -19 64923 UG 19EW5 -13 -18 67889 UG 19EW6 -12 -17 69548 UG 19EW1 -11 -16 81491 UG 19EW2 -10 -15 66914 UG 20EW3 -9 -14 75910 UG 20EW4 -8 -13 77401 UG 20EW5 -7 -12 78476 UG 20EW6 -6 -11 84550 UG 20EW1 -5 -10 105522 UG 20EW2 -4 -9 76644 UG 21EW3 -3 -8 91358 UG 21EW4 -2 -7 98226 UG 21EW5 -1 -6 87241 UG 21EW6 0 -5 88605 Columns TermAxis and MinTermAxis! what do they represent? where are they coming from?
TermAxis is a database field to sort our terms chronologically. It continually updates so the ongoing term is always 0. The most recently completed term is -1 and so on.
MinTermAxis was my attempt to create a field I could use in a calculation to try and sum InTermLeads where the TermAxis was between MinTermAxis and TermAxis.
So, for example, for 21EW6 (UG TermAxis 0), I want to sum the InTermLeads for all rows that have a UG term axis of 0, -1, -2, -3, -4, and -5.
Does that help? I know it can be confusing!
HI AJ,
Not exactly -- one thing I didn't mention this is also at the academic level as the term axis restarts based on Academic Level. I'm sorry I didn't mention that.
So, based on that, just looking at UG rows only and sorting on term axis -- my six term totals would be as follows (last column)
Hi,
Share the download link of your MS Excel workbook and show your expected result there via a formula very clearly. Also, do you want a measure solution or a calculated column solution?
