Reporting across multiple tables using date range?

  • 1
  • 2
  • Question
  • Updated 2 years ago
  • Answered
I have two tables: Leads and Opportunities.
Leads turn into Opportunities. 
I have a need to track the number of leads vs opportunities in a report for a given time period. Looking for conversion ratios. 

Basically, I want to display these numbers side by side in a comparison report. 

How can I accomplish this? 

Thanks,
Evan
Photo of Evan

Evan

  • 266 Points 250 badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
One method is to create at table of dates, called Months,  by importing a sheet from Excel of a long range of YYYY-MM's which represents each month in a text format like 2017-1  (or 2017-01 if you think that reads easier).

Set the Key field of that table to be the YYYY-MM field.

On the child tables of Opps and Leads, make a field of YYYY-MM.

List("-", ToText(Year([lead date])), Right("0" & Month([Lead date]),2))

Then make a relationship back to the Months Table and make a summary field of the # of leads that month.  Then repeat for Opps.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
Hmmm, so that means that the field on the Leads or opportunities table must be bing generated as a lookup field from somewhere else.  Can you tell me which child table you are working on first and what is the source of the date field used to calculate the YYYY-MM?
Photo of Evan

Evan

  • 266 Points 250 badge 2x thumb
Yes, my YYYY-MM field on Leads is looking at another formula. We had some legacy data to import, so some of the Date Created values were inaccurate and skewing our data.  

Is there a way past this?

Thank you for your help, BTW.

ETA: 
I the formula that my YYYY-MM is referencing is basically:
If(
IsNull([Date Created (Legacy)]), ToDate([Date Created]), [Date Created (Legacy)])

Do I need to change that initial formula to a specific type?

Thanks,
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
The issue is that for particularly good reason that I can think of, Quick Base does not allow formulas which are used as the reference field on the right side of a relationship to use the field [Date Created].

There are a variety of solutions, but the easiest is probably to make a new field called [Lead date].  Make it a formula date field with a formula of 

ToDate([That formula field above])

Then change that field just to a Date Field.  The effect of that will be that al those calculated dates will now just be dates.  ie they will remember what they used to calculate to.

Make that a required field and set it to default to Today in its field properties.

Then build your YYYY-MM formula off that [Lead Date] field.
Photo of Evan

Evan

  • 266 Points 250 badge 2x thumb
It Worked! 

Thank you very much! Now putting the reports together should be pretty basic. 

I appreciate you time. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
Great, thx for letting me know.