We help IT Professionals succeed at work.

FM - Leads by Year Chart

Medium Priority
Last Modified: 2012-05-12
I'm trying the simplest chart - to show # of Leads by Year (from 2005 to 2011), but cannot make it work.  I've done several of these charts with no problem, but am stuck with this one.  I've created a calc field that shows only the year and then related this field to itself between two Leads tables, yet all I get is what you see below, even if I put the chart in a footer.  Any suggestions.  I must be missing something obvious.

Watch Question


Sample file.  It's obvious my mistake is trying to show the portal or the chart on one record, but why doesn't it work when I put it in the footer?  Simply trying to show total records by year.  I've done this lots of times in other dbs, have looked at them and tried duplicating them, and cannot figure out what I'm missing.
I think there is a problem with how you are trying to relate things. I don't think your many to many relationship should be used there.

If you are showing records in LEADDATA, lets say you are on a lead record with date of 2005. The horizontal axis will now lookup and find all records with a lead year of 2005. In the vertical axis, the relationship and calc will return the count of those records. So..you have 3000 of them.

Instead add a calculation field in LEADDATA that counts your self-join by year. Dont use that relationship in the graph definition itself.
 You only need the relationship to sum the leads.

Horizontal access would be: DateCrtn_YearOnly
Verticial Access would be your new sum field.

Does that work maybe? You might have to constrain your found set somewhat...


I'm not sure how to create a calc field that counts the self-join by year.  I could create several calc fields, one for each year and calc that way, but not just one.  Am I missing something obvious or how would you do that?


Here's one way I successfully did a similar portal, except I wanted to total by Property instead of by year.  I went to the Property db, which was related to Leads by PropCode, and created a field called LeadsSumByProp and made it equal to a sum field LeadsTotal in the Leads table.  Since the relationship was by Property, it would only show the total for that property.  Then, I brought this field [LeadsSumByProp ] in Property back into Leads in the form of a portal, Prop_X_4Leads, which obviously an X relationship.  

Since this worked, all I have to do is to create a table of years.  However, rather than creating a separate table for Years, Months, Weeks, Days, etc., I was hoping I could use your Calendar table and relate it to itself to do this.  Is that possible?


Example of Leads by Prop portal (which could easily be made a chart).  Question is best way to do this by Year or some other particular date range without a specific table that has only one record per each one of these data ranges.


Any comment on my last comment?


Determined that responder answered my my original question.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.