Reporting against targets with results generated from multiple tables.

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
Hi - Also new to Quickbase.
I have scenario where we record invoices by supplier. We have created a table with all the supplier details, thereafter a second table with all the invoices received from the respective suppliers using a relationship table. This part is working perfectly.
From there we have created a report that summarises the spend by quarter according to the fields required. 
The final strep in this is that we now need to apply the quarter summaries against targets set by our customer. All the the targets are fields exist in a table. 
Does any other user out there have any idea on how we could accomplish this?
Thank you
Photo of Matthew Kearns

Matthew Kearns

  • 80 Points 75 badge 2x thumb

Posted 3 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
I suggest this.
Create 8 date fields representing the quarter period starts for the 4 quarters in the current year and the 4 next year.
Create 8 more formula fields to be the last day of those quarters.

e.g. [Q1 end] would be 
LastDayOfMonth(AdjustMonth([Q1 Start],2)

Create 8 more fields for the Quarter targets.

The reasons I suggest 8 field sets is that you you have the data for a full 2 year period.

Lookup those Q1 starts and Q1 ends etc down to the detail data.

Make 8 summary fields (well get one working and then copy it 7 times.)
to capture sales within that period.

That should do it, once you get those 8 field set lines up nicely on the form.

Basically Get Q1 working and then replicate the whole setup 7 more times.


Photo of Matthew Kearns

Matthew Kearns

  • 80 Points 75 badge 2x thumb

Thank you Coach


Would the fields above be created in the targets table or would an additional table be required? The targets remain consistent over the full period, would one still require the additional 8 fields for the quarter targets?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
The fields would be created on the supplier table.  I imagine that the targets would change from one year to the next, so I just thought it would be easiest to load the Targets for each quarter even if its the same number for each quarter for that year.