Summary field for only a certain month

Question

Hi,I have a revenue table that keeps track of the revenue we receive for wells. We receive revenue about 3 months behind, so I want to create a summary total field on our wells table that totals the payment we receive for the most recent month.

So for example, our most recent revenue is from July. But if I make it "during the last 3 months" then the total will mess up when we get revenue for august and september.
Is there a formula or something that can work that out? or is there a way to filter it that I don't know about?------------------------------Renee Hansen------------------------------

markshnier__you · Answer

It sounds from your post like you only receive one payment per month. So I will answer the question based on that assumption but if that is not the case then we can handle that as well.

Make a summary field on the relationship for maximum payment date and call it [Most Recent Payment Date]. That is a useful feel to have on the Wells record.

Then look up that field down to payments and make a summary field of the payment amount where the payment date equals the value in the field [Most Recent Payment Date]. Call that [Most Recent Payment].

So now the Well record always knows the most recent payment date and the most recent payment amount.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

reneehansen · Answer

I like where this is going for a different issue I was going to ask about.
we need to track when we are missing consecutive payments, and I've been racking my brain on it.But, back to this issue - we do receive multiple payments for a well in a month, and different amounts too, and it could be from a different operator.
Operator 1 could give 3 different payments, while operator 2 gives one full payment, on the same well. (main operator and JV operator).------------------------------Renee Hansen------------------------------

markshnier__you · Answer

I am not sure how the operator figures into your question. You would have to explain if that is a different table in your app or a multiple-choice field somewhere, but if you want to track multiple payments made in the most current month then "no problem".

On the payment table make a formula checkbox field called [Payment was in most current payment month?] with this formula

FirstDayofMonth([Most recent payment date]) = FirstDayOfMonth([Payment date])

Then on your summary field add up the total payments where that check box is "checked".

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

reneehansen · Answer

ok i'm going to give that a try.

I think I can do that for Main operator, and JV Operator.
(Operator is the oil company that drills the wells) - so Operator Payment checkbox and JV Operator checkbox.&nbsp; Right?------------------------------Renee Hansen------------------------------

reneehansen · Answer

How would I track if we are missing revenue for a # of consecutive months? (3 or 6) and track if we have any months with payment, and then a month without payment?​------------------------------Renee Hansen------------------------------

Forum Discussion

Summary field for only a certain month

6 Replies