Forum Discussion

ChrisSwirtz1's avatar
Qrew Trainee
3 years ago

Date Calculation

I was given the following task and I am not sure if it is just the way it is written or what but I am struggling to understand what is being asked. I haven't done much with complex Date Formulas yet. Can someone steer me in the right direction please?

An app contains two tables of data. Each row in the [Customers] table represents a customer, who has had some unknown number of transactions within the stated period (see [Account Opened Date] and [Account Closed Date]). Only the total amount of these
transactions is known (see [Total Transaction Amount]).

Please spread the [Total Transaction Amount] for each customer evenly in each relevant period in the [Periods] table. By "evenly" we mean that you should allocate the transaction dollars into each period based on the number of days in that period, assuming that the period falls into the range from [Earliest Transaction Date] to [Latest Transaction Date] for that customer.

Additional Guidelines:

  • Your solution should return the customer name, period title, effective start date and end date, and a calculated [Allocated Amount] column representing the total transaction dollars allocated to that given period. By effective start date and end date, we mean the start and end dates used in the calculation, not necessarily the period's start and end dates. For example, if the period start date precedes the customer's first transaction, the customer's transaction start date should be displayed, otherwise the period start date should be displayed (see the Sample Output)

  • Make sure to account for periods in which the overlap in days is incomplete. For example, in the first row of the [Customers] table (Howard), the first transaction occurred on 06/05/2017, which only overlaps the dates of Period E by several days. The amount spread to Period E should correctly reflect that overlap.

  • Some customers may have transactions that fall outside of the defined Period A-F dates. You can allow these transactions to drop off since they fall outside of the date range of Periods A-F.
Sample Output:

Customer           Period Title        Effective Start        Effective End        Allocated
Name                                               Date                            Date                        Amount 
Howard              Period E               2017-06-05              2017-06-30          $25,240
Howard              Period F               2017-07-01                2017-12-31            $178,620

As you can see in this sample for the first customer (Howard), the total of $332,000 has been allocated
to three periods: Period E, Period F, and then an extra row that accounts for any amount allocated to
dates after Period F. The allocated amounts total $332,000, and are spread into each period according
to the number of days of overlap between that period's start/end dates and the customer's transaction
start/end dates. In this example, only a small amount is allocated to Period E, since the Howard
customer's record has an earliest transaction date of 2017-06-05 and the period's end date is 2017-06-
30. Period F has an allocated amount that corresponds to the number of days in Period F, since there is
complete overlap between the Howard customer's transaction period and the start/end dates of Period

Chris Swirtz

2 Replies

  • DonLarson's avatar
    Qrew Commander

    You have been handed a Revenue Recognition problem.   I have solved these in the past but never using native Quickbase.  There are two paths I have done this with successfully.

    • Extract the data and use Script to analyze the transaction and then write back into Quickbase the appropriate data
    • Use a BI/BA tool.  I have got customers using both Tableau and PowerBI.  This is my preferred answer for Rev Rec as there are always follow on questions that get progressively more complicated.  The very nature of this issue is high level reporting in the Controller/CFO's office.

    There are two other potential ways to handle it but I have not done them for this.

    • Build a Code Page that will do the calculations.  This leaves you in QB but still is not native functionality, you have to know JS or another development language.
    • Pipelines might be able to do this.  I have built a few but not one that does this type of math. 

    I see a particular issue from your example there is no Period G yet for the other revenue beyond F.   You will need track that and at some point in the future Recognize the final $129K.   This makes me lean to Tableau as you can show in a Tableau report a line for Unrecognized Revenue / Deferred Rev or what every your Accounting Team wants to call it but it is not really data stored in Quickbase.

    Hope this helps.

    Don Larson
    • ChrisSwirtz1's avatar
      Qrew Trainee
      Thanks Don,

      It does help in the respect that I don't feel as bad not knowing what this was as it is definitely beyond anything I have done before. Which is a bummer because this is for a potential new job that I really want but clearly don't have the necessary skills...

      Chris Swirtz