Forum Discussion

EvanBohan's avatar
EvanBohan
Qrew Cadet
9 years ago

Reporting across multiple tables using date range?

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 ra...
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
9 years ago
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.
EvanBohan's avatar
EvanBohan
Qrew Cadet
9 years ago
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,