Forum Discussion
MarkShnier__You
4 years agoQrew Legend
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
------------------------------
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
------------------------------
- ChristinePratt14 years agoQrew CadetHi 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
------------------------------- MarkShnier__You4 years agoQrew LegendDo 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
------------------------------- ChristinePratt14 years agoQrew CadetI 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
------------------------------