Forum Discussion

EvanBohan's avatar
EvanBohan
Qrew Cadet
8 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 ratios. 

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

How can I accomplish this? 

Thanks,
Evan
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 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
      8 years ago
      Thank you for your response.

      I have the Table Month and I have the Text formula for the 2017-01 on Leads and Opps.

      I related One Month to many leads.

      I am having trouble getting the # of leads that month to work. 

      I think I just need a little more explanation. 

      Thank you!
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      Be sure to set the Key field of the months table to the YYYY-MM field.

      On the relationship, be sure that you are using the field you created on the right aside of the relationship ie a formula field that calculates to YYYY-MM

      Post back if you have dome both those things and yet its still not working.
    • EvanBohan's avatar
      EvanBohan
      Qrew Cadet
      8 years ago
      Key field on months table is set to YYYY-MM and made sure my YYYY-MM formula was the reference field for the relationship.

      When I try to created the summary field, I get an Error message:
      "Error
      We are sorry -- at this time, you cannot use this formula and have a summary field using this relationship.