FM - Using a Calendar Table

I want to total all Lead records and chart them for each week.  However, all I know how to do is make a copy of my Leads TO and join it to itself by the calc field that has Year ( DateCreation ) & WeekofYear (DateCreation).  But how would you doing it using a Calendar Table (with a date for each record from aboiut 1990 to 2089?  

See attachment for how I did it -- relating the DateYrWeek field from the Portal TO (LeadsbyWeek) to the main table (LEADS), but how do you do the relationships using a Calendar table?  The broader question is how do you use the Calendar table?  Provide an example or two (an explanation vs. a file is fine to start).
--LeadsbyWeek.JPG
LVL 2
rvfowler2Asked:
Who is Participating?
 
North2AlaskaConnect With a Mentor Commented:
Looking at your example, I have made some changes that may work for you.  

1)  Changed the summary calculation to a count of the related records using the relationship.
2)  Added another TO to join to all the records.
3)  Changed the graph to use the new TO as the results.

While this worked, there were some things I didn't like about the graph; it shows a data point for each record.  This is a problem because each record will have the same value as a result of the calculation.  I did an extensive search for a way to identify only one record for each year, but none met the criteria I needed.  So, in my estimation it as a FAIL.

Next, I created a new table, LeadsGraph, with two columns, Year and Count.  As I played with this I liked it more and more.  First it gave the results I was looking for and second, it allowed a lot of flexibility.  Because it was a new table and you have to enter the year, this also meant that if I wanted to remove a year from the graph I could.  It also allowed for other calculations to be added in, like sales for that same time, giving you a comparison chart between number of leads and amount of sales (for example).

Anyway, here is the sample file. LeadswChart.fp7
0
 
North2AlaskaCommented:
If you can provide a sample file with the tables and columns you would like to use, it would be very helpful.
0
 
rvfowler2Author Commented:
Sorry it took so long, been busy.  Tried adding your date table in but it made the file too large.
LeadswChart.fp7
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rvfowler2Author Commented:
Good idea.  I could have a table for years, one for quarters, one for months, one for weeks, and your table would represent days.  Too bad you can't create a TO relationship off of your main Calendar table so that you didn't have to create separate tables, just separate TOs.
0
 
North2AlaskaCommented:
Let me work on another idea for a bit.  I may get back to you tomorrow...
0
 
North2AlaskaCommented:
My apologies.  Life stepped in this week.  :-)

I have made several attempts but have been unsuccessful in making the joins work.
0
 
rvfowler2Author Commented:
Same for me.  We upgraded to Win7 and are having a terrible time with our DOS database working in the Virtual XP mode environment though initial testing was fine.  Get to this by Friday.
0
 
rvfowler2Author Commented:
Thanks, this works, and sorry for taking so long.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.