Forum Discussion
QuickBaseCoachD
8 years agoQrew Captain
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.
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.
- EvanBohan8 years agoQrew CadetThank 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! - QuickBaseCoachD8 years agoQrew CaptainBe 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. - EvanBohan8 years agoQrew CadetKey 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. - QuickBaseCoachD8 years agoQrew CaptainHmmm, 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?
- EvanBohan8 years agoQrew CadetYes, 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, - QuickBaseCoachD8 years agoQrew CaptainThe 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. - EvanBohan8 years agoQrew CadetIt Worked!
Thank you very much! Now putting the reports together should be pretty basic.
I appreciate you time. - QuickBaseCoachD8 years agoQrew CaptainGreat, thx for letting me know.