Record count for Day, Week, Month, Year > Chart

[bleyden]

Hi All,

I'd like to get a count for how many records were created today, this week, this month, and possibly this year to display on a marketing dashboard (Leads).  I've tried a couple different tutorials but nothing is seeming to work quite right (and are a bit out-dated).  I'd also then like to chart how many leads were generated today for each program (programs on Y axis, today-count on X axis), but i'm having trouble getting it set up since the counts are messed up.  What other details would be important?

Thanks!

[Steve Martino]

How/why are the 'counts' messed up?

Using summary fields and finds with dates are pretty straightforward, and using relationships w/GTRR is another way to go.

Can you post a small sample file with some data?

[comment]

1 hour ago, bleyden said:

I'd like to get a count for how many records were created today, this week, this month, and possibly this year

You could count the records through dedicated relationships. Or a single relationship filtered further to month and week. But in order to reduce the clutter in your schema, consider using a script that performs 3 finds and stores the record counts in variables or global fields. This would be run just prior to displaying the dashboard layout.

 

1 hour ago, bleyden said:

I'd also then like to chart how many leads were generated today for each program

This could be done using a calculation field in the Programs table, counting the related records in the Leads, filtered by current date. Again, to avoid a dedicated relationship you could apply the filter in the calculation itself as =

ValueCount ( FilterValues ( List ( Leads::CreationDate ) ; Get ( CurrentDate ) ) )

However, this will get progressively slower as the number of related records increases.

[bleyden]

When I set it up, I followed this tutorial, and it seems like the week and month fields are working, but the current day count is not.  

Here is the relationship graph I have, and I also attached a PDF of the fields from the Leads table showing their calculation and other information:

I think once I can figure out the "today" count then I can move on to the chart issues... I don't have much brain space left!

Lead Count.pdf

[comment]

29 minutes ago, bleyden said:

it seems like the week and month fields are working, but the current day count is not.  

That's because you have a type mismatch: your count_today field returns a result type of Number, while the match field on the opposite side of the relationship is a Date field (or at least so it seems). In addition, your New Lead Count_Today field does not count anything: it merely copies the value of the first related record in Leads Today.

[bleyden]

Perfect, that cleared that up! I now have the appropriate value types, etc, and that worked.  Now on to the chart... this is what I assume I would have and it is not what i want (otherwise i wouldn't be here lol)

I created these 4 dummy leads:

Here are the two questions I have:  Is it better to have one chart for each time frame (day, week, month), or combine them on the same chart, which will obviously save room?  The chart I was trying to do was just for Day, so it should have come up with two leads for Asking the Question, and 1 lead for QuickLook, but that is not how it worked.  

Thanks everyone!

[comment]

5 hours ago, bleyden said:

Is it better to have one chart for each time frame (day, week, month), or combine them on the same chart, which will obviously save room? 

That's entirely up to you. We can advise regarding the HOW, you must decide regarding the WHAT.

 

5 hours ago, bleyden said:

Now on to the chart... this is what I assume I would have and it is not what i want

The chart needs to reflect data from multiple records (make sure you have Current Found Set selected in the Data Source section) so obviously it cannot be in the Body part. And you need to reverse your axes. If you want to show a bar for each type of count, define each count as a separate Y series.

[bleyden]

Sorry, I'm on the struggle bus with this and not understanding

I'm basically trying to copy this:

On the Y axis, I will have all of the program names, but if I pull program names from Programs::Program Name, I don't have any values pulled.  If I pull the program names from Leads::Program, then I get one item for each lead, and the programs are duplicated if they have more than one lead.  Let's just do this for a record count for today.  I attached a sample file that should have all of the necessary pieces and tried to recreate it and start from scratch to see if I could make my brain work, but still tumbleweeds.  All aboard the struggle bus!

Chart Sample.fmp12

[comment]

See if this simplified demo helps:

 

 

ChartTodaysCounts.fmp12