Discussions

Expand all | Collapse all

Reporting across multiple tables using date range?

  • 1.  Reporting across multiple tables using date range?

     
    Posted 06-06-2017 18:17
    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


  • 2.  RE: Reporting across multiple tables using date range?

    Posted 06-06-2017 19:39
    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.


  • 3.  RE: Reporting across multiple tables using date range?

     
    Posted 06-06-2017 20:47
    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!


  • 4.  RE: Reporting across multiple tables using date range?

    Posted 06-06-2017 21:50
    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.


  • 5.  RE: Reporting across multiple tables using date range?

     
    Posted 06-06-2017 21:54
    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.


  • 6.  RE: Reporting across multiple tables using date range?

    Posted 06-06-2017 21:56
    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?


  • 7.  RE: Reporting across multiple tables using date range?

     
    Posted 06-06-2017 22:02
    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,


  • 8.  RE: Reporting across multiple tables using date range?

    Posted 06-06-2017 22:17
    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.


  • 9.  RE: Reporting across multiple tables using date range?

     
    Posted 06-06-2017 22:58
    It Worked! 

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

    I appreciate you time. 


  • 10.  RE: Reporting across multiple tables using date range?

    Posted 06-07-2017 01:08
    Great, thx for letting me know.