Including X-values with '0' data

[Wickerman]

Let's say I have a chart that shows how many Visitors a museum has on each day of the week. I sort by DayOfWeek and put this Field on the X-Axis and a Count summary field on the Y-Axis. Dandy.

. . . except that if there were no visitors on a given day, the chart doesn't include that day at all -- you get something like

Mon Tues Thurs Fri Sat Sun along the bottom.

In this case, and many others, though, you *want* to have 'Weds' included with a 0 data point. But that would in effect be charting the absence of a record.

What's the workaround for this? Must be a pretty common need, I'd think?

[comment]

But that would in effect be charting the absence of a record.

Exactly - and the solution is to have that record. Assuming you always want to show an entire week, define a new table with seven records, where each record will calculate its own date from a global date selected by the user, and count the associated records.

[Wickerman]

Not sure I follow you. I've got a Table for 'Visits' where each record describes a single visitor's experience, and there's a Date field there, and a Calc field that converts that to a Day of the Week. So would this new Days of the Week table be related to this Visits Table by matching the Day field (Monday = Monday)? Or is this a free-standing tab;e? Or what?

So, I have the user enter start and end dates into a couple of Global fields . . . but I'm not clear on how to count the records...sorry to be a little slow on this...

[comment]

Here's a simple example:

WeeklyChart.fp7.zip

[Wickerman]

Wow -- that's really great. I had to stare at that 'cDate' key field a long time before getting my head around it, but now I see that it sets the date for each day of the week based on the date selected by the user, and then pulls the related records through from the VIsits.

I'm afraid though, I didn't quite get across the situation accurately. I'm not just trying to get the visits or any given week that the global date is a part of -- I'm trying to also allow for the charting of visitor patterns over longer stretches of time -- say an entire year. The point is the Museum is interested in "Fridays" as opposed to "Tuesdays" -- not just looking at stats for a single week.

So, is there a way to aggregate these data in that way?

[comment]

I'm not just trying to get the visits or any given week that the global date is a part of -- I'm trying to also allow for the charting of visitor patterns over longer stretches of time -- say an entire year.

That should be even easier:

WeeklyChart2.fp7.zip

[Wickerman]

Superb. I expect someday I'll get to the point where I can just come up with this stuff myself, but until then this forum is a real lifesaver.

Can I pester you for one more variation on this theme? As it turns out, there will be times when the user wants to select the relevant Visit records not by a date range, but on some other varied basis -- like a particular "kind" of visitor or whatever. Is there some way that I could make the chart display records from the current Found Set, rather than dates? I'm thinking the User would start on a layout in the Visits contexts, perform a search and then hit a button that would generate this chart based on the current Found Set. I'm guessing maybe some method of gathering all the primary keys of the visits in a return-delimited list in a global field and then somehow basing the relationship on that?

I really am learning though -- you should see the questions I was asking 4 years ago!

[comment]

I am afraid this method will not work with a found set, so it's not a matter of "one more variation" - you need something completely different.

If you can limit yourself to a script-generated chart, you could use something like FastSummaries to quickly produce a return-delimited list of 7 values (including zeros where appropriate) and use that for your chart's Y axis.

you should see the questions I was asking 4 years ago!

LOL, you should see the answers I was providing 4 years ago... :smile: