Discussions

 View Only
  • 1.  Need help with month to date formula

    Posted 06-25-2020 10:02
    Edited by Christine Pratts 06-25-2020 16:13
    Hello, 

    I have a field that tracks the number of transactions for a client. Each day, a new record is created adding the new day's activity (# of transactions for that day) per client. I need help writing a formula that will calculate the month to date value of that field so that as the new activity is added, this field will calculate the rolling month to date totals. 

    Thanks!

    ------------------------------
    Christine Pratts
    ------------------------------


  • 2.  RE: Need help with month to date formula

    Posted 06-25-2020 18:50
    You can make a table called Months with dates of the first of the month using excel and load up 20 years worth of dates.  120 records.  Make the date the Key field.

    Make a relationships to transactions and let it create a new field for you called Related Date.

    Edit that field to rename it to be called [First of the Month] and change the type to be formula date with the formula 
    FirstDayOfMonth([my transaction date field])

    Make a relationship where One Month has many Transactions based on that[First of the Month].

    Make Summary of total # of transactions called [MTD Transactions].

    ​Look up that field down to Transactions.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Need help with month to date formula

    Posted 06-30-2020 10:33
    Hi Mark,

    Thanks for your response. I tried what you suggested. The issue that I came across is that the MTD transactions field that I created isn't behaving the way I need it to. It's calculating the MTD but the MTD totals changes across the month...

    What this formula is doing:
    6/26/20     20,251 Transactions     42,071 MTD Transactions
    6/26/20     11,596 Transactions     42,071 MTD Transactions
    6/27/20     10,224 Transactions     42,071 MTD Transactions

    What I need it to do:
    6/26/20     20,251 Transactions     25,251 MTD Transactions
    6/26/20     11,596 Transactions     31,847 MTD Transactions
    6/27/20     10,224 Transactions     42,071 MTD Transactions

    So that each date has a cumulative total of transactions Month To Date.

    Thanks!

    ------------------------------
    Christine Pratts
    ------------------------------



  • 4.  RE: Need help with month to date formula

    Posted 06-30-2020 10:47
    Do you need to have the MTD recorded in a field on that record or would it be good enough just to have a report. 

    If a report is good enough, then there is a KISS solution.  That is to create a summary report to summarize the # of Transaction by day and include the field for # of transactions and also the same field again, but select the option to have a running total down column.​​

    If you need the data record  each night, then that can be done as well but the setup is more complicated.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Need help with month to date formula

    Posted 06-30-2020 10:57
    I need the data record for each night. The field is actually to be used in a larger formula that calculates the revenue based on a tiered pricing. The tiered pricing is based on the cumulative # of transactions ​for the month. Once the transaction totals reach the next tier, the rate changes. When we bill, we break out the # of transactions billed on each tier and it also helps us to see (for future pricing models) how quickly a client moves from one tier to the next.​

    ------------------------------
    Christine Pratts
    ------------------------------



  • 6.  RE: Need help with month to date formula

    Posted 06-30-2020 11:40

    I realize now that this is all "Per Client".

    One client has many daily transaction records. 
    We have a bit of an obstacle in that this process will run after midnight so I would make a flag on the daily transaction records to calculate to true of the record is to be included in the nightly summary. The reason is that on the first of he month at midnight, you still need to get the totals for the MTD for the previous month.   ​

    So  now the Client record knows the MTD totals up until last night.  Look that up into the daily transactions table.

    Make a new field in Daily Transactions to called  [MTD Frozen]
    Set up a saved table to table copy to merge the daily transaction records into themselves based on Record ID# and filer where the date is the today (or maybe yesterday - I'm not sure the timing of when these records get created).  Then map the MTD into the [MTD Frozen]

    Set up a daily Automation to  run  the save table to table import.

    I think that will work but the tricky part is you have to be very careful of how do you summarize the transactions because the automation will run shortly after midnight. And of course we only want to update the most recent daily transaction records.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Need help with month to date formula

    Posted 06-30-2020 13:57
    I gave this a try. I'll need to see how it works after a few days. 

    Thanks!

    ------------------------------
    Christine Pratts
    ------------------------------



  • 8.  RE: Need help with month to date formula

    Posted 07-10-2020 10:56
    Hello,

    It turns out this question is a little more complicated than I thought. 

    The data that I'm working with captures daily transactions for a client. However, the transactions (entered manually) are not always entered on a daily basis. For example, Friday, Saturday and Sunday's data is entered on Monday. Monday's data is entered on Tuesday, and so on. But, at times, there can be a delay in obtaining transactional activity - system or reporting issues and it may be days (I've learned) that data can be held up and then all that data can be entered in batches.

    So, I need to calculate the MTD activity but the automation to run that calculation (based on Date = yesterday) won't work correctly if numerous records for different dates are all inputted on the same day. 

    Does that make sense?

    Thanks so much for your help!!!

    ------------------------------
    Christine Pratts
    ------------------------------