Anyone who deals with large volumes of data has most likely used grouping to make sense of it. Why? Because grouping data into meaningful buckets can reveal patterns that may not be apparent in a sea of detail.
We use groups consciously and unconsciously all the time. We organize ourselves into affinity groups, arrange our bills by denomination in our wallets (you don’t?), tell jokes – (An American, an Englishman and a Frenchman walk into a bar…). Often, the best jokes and stories are short and have a great punch-line.
Grouping condenses and delivers the punchline for data. It breaks down the data into easy-to-understand portions by organizing (sorting) into different categories then calculating aggregate values – counts, sums, averages etc.
If you have been using Dynamics GP for a few years, a detailed sales report might contain tens of thousands, even millions, of records. How does grouping help?
Let’s say we have a million rows of sales data. Here are some ways to look at it.
To start, we might simply say: 1 million records. Total Sales $123,456,789.00
When grouped by transaction type, there are 900,000 Invoices, totaling $161,110,798, less 100,000 credits totaling $37,654,000 yielding a net revenue of 123,456,789. Interesting: the average invoice is about $179, while the average credit is more than $375. What’s going on?
Or, say the data reveal that the average Order to Invoice lead time is 5 days. Can we improve on that? The answer to that might require grouping the data by lead times and items. Which in turn shows that some items shipped in one day, while some were delayed months. Now that is useful information. What’s causing those month-long delays? Can we get a new supplier? Build a better mousetrap?
How do you do this? Pivot Tables.
I started using pivot tables early on – (In Excel / Office 95 as I recall). The ability to “slice and dice” data in ways that satisfied my curiosity or answered my questions delighted me (yes; I’m a geek). I would start with a group or two, then break out into sub-groups and more sub-groups. Then start again, using different criteria.
Pivot tables let you add fields to the report, allowing you to “drill down” and get more detailed summaries, or remove fields from the report, effectively zooming out to get a broader view. This ad-hoc, interactive analysis provides immediate answers, and in some ways, almost feels like a conversation. I can ask question like Why do I notice a slow-down in sales in January? What is the best time to call a particular customer or a group of customers? What was our revenue by month? By month and Sales person? By salesperson, and geography?
Pivot tables are built on a table of data – i.e. based on a lot of detail, they create a separate summary/grouped view of the data. Pivot tables have three primary areas – rows and column areas are used to group data in two dimensions. The data area shows the summary values.
Data fields such as Customer, Item, City, State, and User ID are often used to group data. Date values too are often grouped into months, quarters and years or other periods (as with the aging groups above). You can move fields from row to column or column to row as you prefer. Generally, longer groups perform better in rows, while smaller groups perform better in columns.
Even though today a number of options exist for creating pivot tables, for many people Excel continues to be the only or preferred way to analyze data. And it is a fantastic application. Still, using Excel’s Pivot tables with data from another system (like GP) can be cumbersome. Often you must you export data from your system, and then go through a series of steps to import the data into Excel and create the result you want. (And if the data change or you leave out an important field, you have to do it all over again.)
Mercury allows you to define grouping rules enabling you to create custom groups based on your data. (And, as a byproduct, explicitly sort the group values too.) The aging buckets in the following example represent a custom group.
Here is another custom group.
In Mercury, any grouping method can “attached” to one or more fields and used in multiple reports, saving you the tedium of recreating the same rules for each report. Once created, a custom group is always ready and available to do its work.
