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
------------------------------
Original Message:
Sent: 06-25-2020 18:50
From: Mark Shnier (YQC)
Subject: Need help with month to date formula
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
Original Message:
Sent: 06-25-2020 10:02
From: Christine Pratts
Subject: Need help with month to date formula
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
------------------------------