Sum across all subsequent items in a Year:Month Timeline
I have an item full of values across a Year:Month timeline (payment tranches). In another item I’d like to calculate a boolean value that flags the position of the last payment, and my first instinct was to sum a sublist containing all subsequent values, and flag the first position in which that sum was = 0.
[code:3065oeys]B5 = sum(B1:Year[THIS]:Month[THIS] .. B1:Year[LAST]:Month[LAST]) – B1:Month[THIS][/code:3065oeys]
The formula for B5 was my first attempt, but it clearly is wrong (see "B5 Join"; it’s superfluously adding the values for M12 from Y2 and Y3 to every sum). Can anybody tell me [i:3065oeys]why[/i:3065oeys]? [b:3065oeys][i:3065oeys]I[/i:3065oeys][/b:3065oeys] think my formula is saying : "sum all B1 items starting from the current position all the way to the end, the subtract the value of B1 at the current position".
[code:3065oeys]B5 v2 = sum(sublist(B1:Year:Month, #Year:Month + 1, count(Year) * count(Month)))[/code:3065oeys]
The formula for "B5 v2" was my 2nd attempt. It is correct, however:
[list:3065oeys]What elegant way is there to avoid a #VALUE in the last position?[/list:u:3065oeys]
[list:3065oeys]Is there a more straightforward way to calculate the index of the last position of a Year:Month timeline?[/list:u:3065oeys]
[list:3065oeys]Why am I getting this particular error message?[/list:u:3065oeys]
[code:3065oeys]B6 = sum(B1:Month:Year) – sum(sublist(B1:Month:Year, 1, #Year:Month))[/code:3065oeys]
The formula for B6 was my final attempt. The results are what I expect, but again, this seems like an inelegant way to mark the last non-zero value’s position.
As an aside, what’s the "English language" translation for the following, and why aren’t they all equivalent?
[list:3065oeys]B1:Month[NEXT][/list:u:3065oeys]
[list:3065oeys]B1:Year:Month[NEXT][/list:u:3065oeys]
[list:3065oeys]B1:Year[THIS]:Month[NEXT][/list:u:3065oeys]
Jlin,
In regards to your first question formula B5:
I too have received a similar error when trying to do a running sum. Here is the explanation from Quantrix that I received on this question.
[quote:ovcd8n2d]Quantrix’s timeline features are designed to eliminate having to write a formula to ‘cross dimensions’ to bring data from period 12 of the previous year to previous 1 of this year. This works great for most cases, but when doing rolling averages that need to reset each year, it does not work so great. Basically the message saying it can’t use the timeline ranges, which is effectively true, since the formula is stopping the timeline recursion formula from calculating between years basically. [/quote:ovcd8n2d] This sorta makes sense to me.
In regards to your second question B5 v2:
Instead of having the "to" element of the sublist function be count(Year) * count(month) simply use Max(#Year:month) and wrap this entire formula in the clearerror function. This also removes the "Too few Items from category B…" warning.
[code:ovcd8n2d]B5 v2=clearerror(sum(sublist(B1:Year:Month,#Year:Month+1, Max(#Year:Month) ) ), "")[/code:ovcd8n2d]
In regards to your last question about the English Equivalent. I think they all are the same if you are NOT using timeline. However when using timeline the last example does not work and this is because of the explanation offered by Quantrix as noted in question 1.[img:ovcd8n2d]https://flic.kr/p/AJN5ST[/img:ovcd8n2d]
See the attached model for this example.
I hope this helps.
–Rich Lopez
