Skip to main content
Question

Reporting across multiple tables using date range?

  • June 6, 2017
  • 0 replies
  • 114 views
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
      This topic has been closed for replies.

      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.
      Terms & ConditionsAccessibility statement