FM 13: Virtual List Charts, part 2

Picking up where we left off in part 1, today we’re going to take a look at examples 2 through 6 in the Virtual List Charts demo file (the demo has been updated since part 1, so I recommend downloading a fresh copy).

We covered example 1 and most of the general concepts last time, so today we’re mainly going to touch on specific points of interest, but to briefly recap…

• we’re using Mikhail Edoshin’s fast summary technique
• to summarize values from Web Visits and Sales Data
• and push those values into $$variables[withReps]
• and then parse those values into charts
• displayed via a special “virtual” table
• using Bruce Robertson’s virtual list technique
• the virtual table is named “v” on the Relationships Graph
• and is not related to any other tables

Also, our six examples use these four chart layouts as follows:

And  since we explored example 1 last time, the first example we’re going to look at today is…

Example #2 – web visits: line chart, all states

Here’s the script we’re going to run.

There are two ways to chart this data, but it doesn’t have to be an “either/or” decision; since virtual charts make it easy to get multiple perspectives on the same data in a single chart run, let’s choose both…

…and after a few seconds, we see the weeks charted both individually and cumulatively.

(If you’re curious about cumulative charting, it was discussed in detail in this article: Weekly Sales Comparison Charts.)

We explored the ideas behind the chart generation scripts in part 1, so don’t need to spend much time on scripting this time around. I will point out however that this example’s script uses three loops:

• outer – one iteration per chart
• middle – one iteration per year
• inner – one iteration per week

Note that the chart can be toggled between List view…

…and Form view, and the Y axis will become more granular as a result — you may need to resize the chart window to see it (and if you resize horizontally, the X axis labels will get more breathing room).

Here’s the salient portion of chart setup, and compared to the chart we saw in example #1, there are two major differences: a) the chart type is Line, not Column…

…and b) there are multiple data series for the Y axis, which in this case is a fancy way of saying there are multiple chart lines and corresponding names. The names will be parsed from $$yName[rep]…

…using the GetValue function and will appear in the legend, color coded to match the appropriate chart line like so:

Note that the maximum allowed number of data series is 12. In this example, assuming the current year is 2014, we will be charting nine of those years, but since a) one of the goals is to create reusable chart objects, and b) there’s no harm in defining (but not necessarily using) all 12 series on a line chart, that’s what we’ve done.

A few observations…

• $$variable[reps] correspond to records in the virtual table, and
• we’re generating two charts, so
• we’ll be limiting our found set in the virtual table to the first two records in that table, where
• the serial number of those records will be 1 and 2, and
• the $$variable rep [1] is considered to be implicit, and therefore not shown, e.g., in the data viewer screen shot above we see $$yName, not $$yName[1]

Finally, we come to the $$yData[rep] variable, a two dimensional array that looks like this…

…with each row corresponding to a Y axis data series, and bullet characters delimiting the individual values within each series. Here’s how it’s parsed:

Note that for our earliest data series (year 2006) we had no values for the first eight weeks, and whereas in example 1 we chose to insert zeros as placeholders, here we simply delimit the missing values, which allows the line to begin in mid-air, and in the correct location:

(This is an example of “minding the gap”, which was discussed in part 1.)

Example #3 – web visits: line chart, 1 per state

In this example, we’re going to produce 50 charts, one for each U.S. state.

As we saw in part 1, the Web Visits table is related to the States table via the “st” field…

…and when the user clicks the button, they are offered the option to sort the charts by either state name or population ranking.

This is the slowest of the examples, but it’s not too bad — locally on my machine it takes about 18 seconds to generate the 50 multi-series charts.

The script (“web visits: line charts for each state”) uses three loops:

• outer – one iteration per state (i.e., per chart)
• middle – one iteration per year
• inner – one iteration per week

In the outer loop, the $$breakField[rep] will be populated like so…

…and as we saw in part 1, this variable is parsed by the v::break field like so…

…or like this if the user chooses to sort by population ranking (since v::break is a text field, the values are zero padded to facilitate proper sorting).

Example #4 – sales: column charts, 1 per year

Examples 4, 5 and 6 are based on Sales Data, which is linked to Employees like so:

Example 4 uses the same chart layout we saw in example 1, and since there’s only a single Y data series, the script will only require two loops:

• outer – one iteration per year (i.e., per chart)
• inner – one iteration per employee

There isn’t really a whole lot to say about this example, but I’ll point out that the chart bars are sorted by employee last name even though the first name is displayed…

…thanks to the highlighted lines in the “sales: column charts for each year” script.

Note: sales::id_employee is included in the sort criteria, because it is used as the break field for the GetSummary function that populates $$yData[rep].

Example #5 – sales: column charts, comparison

Here the goal is to answer the question, “How did our salespeople do over a given time period vs. that same time period one, two and three years ago?” with the time periods being yesterday, the last seven days, the last 30, 60, 90, 180 and 365 days.

(Note the “gap minding” in the first chart.)

Of all the examples, this one required the most trial, error and debugging. Even though there are four Y data series, I ended up using just two loops:

• outer – iterates 28 times (7 charts * 4 time periods per chart)
• inner – one iteration per employee

In these charts, it’s easy to see individual employee sales, but what about the total dollar amount for each time period? That would be a useful number to see, so I decided to include it in the legend…

…with help from a custom function, WholeNumberWithCommas.

And who could resist multiple opportunities to employ the under-utilized Choose function?

Example #6 – sales: column charts, calendar-style

I call these “calendar-style” charts because they resemble the month entries on one of those “year at a glance” calendars.

Here it is in layout mode…

…and since there are three charts per record in the “virtual” table, each of these records now corresponds to three $$variable[reps], instead of there being a one-to-one correspondence between them (as in the preceding five examples).

Also, the first column will always have a chart, but the second and third columns may or may not:

And rather than display an empty shell of a chart, columns 2 and 3 are optionally hidden like so:

Finally, since we only want the heading in bold to appear once per year we’re going to configure this layout much as we would a summary report…

…with v::break placed in a leading subsummary…

(and as we saw in part 1, v::break is defined like so…)

…with $$breakField[rep] populated as per this highlighted step:

…and sorted like so:

In Conclusion

This two-part article and accompanying demo file have barely scratched the surface of what can be done with this technique. The combination of the Fast Summary and Virtual List techniques is extremely powerful, and perhaps you will find some uses for Virtual Charts in your solutions — if so, I hope you’ll let me know.