This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal


Mar 12, 2015, 8:36 PM
3 Posts

Cumulative Sums

  • Category: Application Development
  • Platform: Windows
  • Release: 9.0.1
  • Role: Developer
  • Tags:
  • Replies: 4

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!

Mar 13, 2015, 2:33 AM
191 Posts
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.
Mar 13, 2015, 8:36 AM
30 Posts
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)))

 

Mar 13, 2015, 3:26 PM
3 Posts
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!

Mar 13, 2015, 8:40 PM
3 Posts
Thanks!

Thanks, Christopher. Your suggestion was right on! Truly appreciate the help!


This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal