Jump to content
Sign in to follow this  
bleyden

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

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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:image.png.838d29a90abab96dc902436990a46c0d.png

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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)

image.thumb.png.f9318698d7842e83eb10e15ddf117501.png

I created these 4 dummy leads:

image.thumb.png.e29da4831737c07cf8e9592467b05a1e.png

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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

I'm basically trying to copy this:

image.png.ce6a167e65e9a3f880a8eb826778a5b8.png

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.