Running Total, Not Date Based

[heidibb]

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?

.

[aj1973]

Hi @heidibb 

Is this what you want?

[heidibb]

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)

[Ashish_Mathur]

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?

[heidibb]

Hopefully you can see the file here:

https://docs.google.com/spreadsheets/d/1nfKyzRQcJmi_o4vRwRaNgPY0TZJtjhPE/edit?usp=sharing&ouid=10276...

Formula is in the last column. It's a simple sum of six rows. File is sorted by academic level and then by term axis so the correct six rows are being summed.

It would be a measure solution that I'm looking for.

Thank you for taking a look!

[Ashish_Mathur]

Hi,

I get an Access Denied message.

[heidibb]

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

[aj1973]

Columns TermAxis and MinTermAxis! what do they represent? where are they coming from?

[heidibb]

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!

[aj1973]

Hi @heidibb 

Is this starting looking good!