Hi, all, and thanks in advance for the help. I’m developing an application to track utility bills, and am experiencing some difficulty. Briefly, every month has me creating a document that reflects certain details of that month’s utility bill. On the document, there are a few fields, including ones depicting the date the meter was read, the kilowatts used, a difference score (from baseline performance - expressed in kilowatts), and the date the document was created.
On the form/document, I’d also like to add a field that displays a cumulative sum of the difference scores. For example, we’ll start in calendar year 2015. January’s bill features a difference score of 25. Since it’s the first month, its cumulative total is also 25. February features difference score of 30. It’s cumulative total, therefore, is 55. March has a difference score of 10; the respective cumulative total is 65. And so on.
I’m having trouble finding an easy way to use formula language to code this field. Apologies if this is misdirected or unclear. I really do appreciate any advice you could offer. Thanks!
Subject: Very easy in @function
Two ways
Either make the form inherit fields from the previous month -so you can only create a new month from the previous month’s document
Set up a computed when composed field called ‘lastmonthscumtotal’ with a value of cumtotal (or whatever your field name is)
Make the computed cumtotal field =lastmonthscumtotal + thismonthsdifference
This has the problem that if you go back and correct a figure, all later months will be wrong
So the alternative is
Create a view of the bills categorised by year
Make the second column the difference
Your formula for lastmonthscumtotal is now
list:=@dblookup(“”:“nocache”;“”;“viewname”;year;2);
checkedlist:=@if(@iserror(list);@return(0);list);
@sum(@subset(checkedlist(monthnumber)))
Subject: Thanks!
Thanks, Christopher. Your suggestion was right on! Truly appreciate the help!
Subject: Sounds like a job for LotusScript
I’m not sure offhand of how you’d do that in formula language, but LotusScript would be quite easy. Assuming you have a handle to the current month’s document, get a handle to the last month’s document that stores the rolling total. Extract the value of that field, add it to your current month’s total, and store the sum in the current month’s rolling total field.
Subject: Great ideas
Thanks, you two. I’m going to try Christopher’s approach (my LotusScript skills are minimal). I’ll let you know how it goes.
Cheers!