Discussions

Expand all | Collapse all

Summary field for only a certain month

  • 1.  Summary field for only a certain month

    Posted 10 days ago

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


  • 2.  RE: Summary field for only a certain month

    Posted 10 days ago
    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
    ------------------------------



  • 3.  RE: Summary field for only a certain month

    Posted 10 days ago

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



  • 4.  RE: Summary field for only a certain month

    Posted 10 days ago
    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
    ------------------------------



  • 5.  RE: Summary field for only a certain month

    Posted 10 days ago

    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.  Right?



    ------------------------------
    Renee Hansen
    ------------------------------



  • 6.  RE: Summary field for only a certain month

    Posted 10 days ago
    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
    ------------------------------



  • 7.  RE: Summary field for only a certain month

    Posted 10 days ago
    I suggest that you create 12 summary field son our Parent Wells record. 

    [Payments current month] with a filter of payment date is during the current month.
    [Payments last month] with a filter of payment date is during the previous 1 months.
    [Payments 2 months ago] with a filter of  

    ALL
    payment date is during the previous 2 months.
    payment date is NOT during the previous 1 months.

    Continue that pattern up to 12 months ago.

    Then you will have buckets on your parent records which show how much payments were made in each of the recent 12 months. 

    You can also create 1`2 date fields to show the date of he first day of the month for each of the 12 buckets.  if you align gthen with your 12 buckets than you will know what the month is for each bucket.

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