ReneeHansen
Qrew Member
3 years ago

# Summary field for only a certain month

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
------------------------------

### 6 Replies

• 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
------------------------------
• 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
------------------------------
• 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
------------------------------