asked on

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

North2Alaska

If you can provide a sample file with the tables and columns you would like to use, it would be very helpful.

rvfowler2

ASKER

Sorry it took so long, been busy. Tried adding your date table in but it made the file too large.
LeadswChart.fp7

ASKER CERTIFIED SOLUTION

North2Alaska

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rvfowler2

ASKER

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.

North2Alaska

Let me work on another idea for a bit. I may get back to you tomorrow...

North2Alaska

My apologies. Life stepped in this week. :-)

I have made several attempts but have been unsuccessful in making the joins work.

rvfowler2

ASKER

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.

rvfowler2

ASKER

Thanks, this works, and sorry for taking so long.