I need to divide the monthly sum of two fields in unrelated tables on a 12 month rolling basis and show in a report?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Let me describe in more detail.  The end result would be a report with 12 columns, one row, and a % value in each column.  

There are multiple expense records dated in every month of every year in the expense table.  There are also multiple invoice records dated in every month of every year in the invoice table.  

I need a 12 month rolling report that takes the total expense amount for any given month and divides it by the corresponding months' total invoice amount, for each of the past 12 months.  How to do this?

Secondary Question:  Is there a way to do this using a summary report with the date field as the crosstab column grouped by month?
Photo of Robin CC

Robin CC

  • 200 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
No problem.  
Create a new table with a key field called YYYY-MM.

Using excel load up the YYYY-MM column for the next 10 years and import that column into QuickBase.  Set the Key field of the QuickBase Table to that YYYY-MM field.

On each of the cape tails table make a field called YYYY-MM with a formula of

ToText(Year([my date field])) & "-" & right("0" & totext(Month([my date feld])),2)

Use that field to make a relationship back to the parent YYYY-MM table

Then all you need to do is to use Summary fields to get any totals up to the Parent Table and do your division  and ratios there.

I.e. You cannot do this I a Summary report off the detail.  You need to get the details summaries into another table first.
Photo of Robin CC

Robin CC

  • 200 Points 100 badge 2x thumb
Hi Mark. Thanks.   I have a couple of questions.  The key field of the summary table is supposed to be a text field correct?   Also, when you say to "Use the formula field to make a relationship back to the parent YYYY-MM table", what does this mean exactly?  Am I to change some setting within the relationship or the reference field??
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Right, the Key field will be a text string like 2015-03

On the details table, you will need to make a formula that calculates to its [Related Parent] field, so  a date like March, 2, 2016 needs to come out by formula as 2016-03 in the YYYY-MM field on the details record.
Photo of Robin CC

Robin CC

  • 200 Points 100 badge 2x thumb
Got it to work!  The reason I was confused is because I already had the relationship in place prior to making the formula field,  and therefore, I could not see how to make that field the reference field.   So I had to delete the relationship and make a new one with that field as the reference field.   In fact, I've used QuickBase for 2 years now and I have never needed to change a reference field like that.  Learn something new everyday!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Great!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
btw, you may have had a ref field of [Related Parent] and then realized it needed to be a formula field.  You can just change the type to be a formula field  and edit the formula. That way you would not have to delete the relationship and start over.