Brainstorming Idea (Feature Request) : Support for generating pivot tables

Motivation

As I start to "grow up" in my use of TWiki tables, from previously using them for basic lists to now more and more sophisticated spreadsheet based TWikiApplications, I find myself wanting for the rich features of a pivot table.

To date, the YetAnotherXpTrackerPlugin and FormPivotPluginDev (5 years old) are the only two topics that respond to Google:site%3Atwiki.org+pivot%20table

Description and Documentation

Support for generating pivot tables from SpreadSheetPlugin data or from data stored in regular TWiki Tables

What are PivotTables ?

From A Complete Guide to PivotTables: A Visual Approach :

PivotTables are a feature of Excel that allows you to see patterns and trends of large amounts of data in a short amount of time. You can take lots of pieces of information and get insights about how the data is related. If you want to look at the same data insights from additional perspectives, you simply rearrange, or pivot, the data in the PivotTable accordingly so that additional insights swing into view.

As you use PivotTables to help you analyze and compare information, you begin to make sense of what at first seems like unrelated information, turning data into the fuel that helps you to make key decisions in faster time.

For example, using PivotTables, you can take thousands of individual sales transactions and present them in a table that provides a summary view of sales by calendar month that fits nicely within your computer screen without scrolling. You could then quickly transform the summary view into sales by geographical store location for comparison. Lastly, you could quickly compare sales by both calendar month and store location at the same time with just a few clicks.

Good Uses for PivotTables

Again, from A Complete Guide to PivotTables: A Visual Approach :

To help you understand the scenarios that PiovtTables are best used for, assume the facts and numbers in Figure 1-14 (not shown) consist of hundreds or even thousands of rows of data like sales transactions for a given calendar year spread out over several outlets in a large geographical area. How would you begin to make sense of all that data ?

It would be hard to visually and mentally process thousands of rows of data, not to mention tens of thousands or hundreds of thousands of rows. Summing or averaging the rows is usually not enough. Outlining in Excel is not very flexible either. You will most likely need to organize related data together along several groupings.

When you want to look at the data from a different perspective, you need to start over and organize the data into different groupings. These groupings don't necessarily follow the pattern of the data as it's presented in the worksheet.

The nice thing about PivotTables is that changing the groupings of data is quick. You can quickly rearrange, or pivot, the data to see information from whole new perspectives. You can ask all sorts of questions about your data without affecting the underlying data itself.

Following are good candidates for data that can be measured by PivotTables:

• Data that is preseented in rows with the same number of columns per row. For example, the geographical location that applies to each fact and figure is placed in the first column, the date that each fact and figure was collected is placed in the second column, and so on.
• Numerical data or columnar text that is restricted to predictable lists of choice or values; for example: true/false, male/female, only the numbers 1 through 10, north/south/east/west, and so on.
• Data that is spread into, and organized by, logical relationships; for example, time (years, months, weeks, days, hours, minutes, and seconds), geography (continents, countries, regions, zones, states, cities, and communities), and so on.
• Data that is measured consistently; for example, the same currency, date formatting, metric measurement system, and so on.

Implementation

Noting that I haven't the first idea about how this would be implemented, I've changed the topic classification to BrainstormingIdea / Feature Request

-- Contributors: KeithHelfrich

Discussion

-- KeithHelfrich - 17 Feb 2007

That sounds like a useful enhancement of the SpreadSheetPlugin. What spec do you envision?

I am also toying with a related idea: CALC already has list functions. It would be useful to add hash functionality.

-- PeterThoeny - 17 Feb 2007

It would be a useful enhancement indeed! As an experienced code user but amateur code writer, I'm afraid that I don't even know what a code spec looks like (much less how to write one). Though (in addition to adding new content to this topic above) I can offer some thoughts on the subject to get it started :

• PivotTables have become an indispensable tool for me in data analysis
• The same is true for the wide variety of PivotCharts available in Excel. In fact, the PivotCharts are even more indispensable.

• For that reason, when implementing PivotTable support in TWiki, I wonder if it would not be better to send the TWiki data to excel and allow for the real pivot action to be performed within Excel ?
  • This would subtract from the overall joy of accomplishing the same thing in TWiki directly, and it would require that the user have Excel available to them to use
  • But it would avoid the re-invention of a perfectly good wheel and allow for the immediate exploitation of all the feature rich charts and graphs that are available from Excel when working with PivotTable data.

Thus, one decision to make straight off is whether we're trying to reproduce the PivotTable functionality directly in TWiki with SpreadSheetPlugin code ? Or whether we're trying to build a "bridge" through an API that uses the existing features of MS Excel and makes them accessible from TWiki ?

I don't know about which API's are available, but the novice programmer in me dreams of simply attaching an excel worksheet to the topic and having TWiki use those API's as an engine to present PivotTable functionality in the browser.

If re-creating the PivotTable features directly in TWiki, the following functions would be required :

• From the list of columns available in a given set of table data :
  • Add a field to the row area
  • Add a field to the column area
  • Add a field to the data area
  • Add a field to the filter area
  • Refresh the pivot table

Thereafter, supporting the large variety of PivotCharts would open up a whole new world for TWikiApplications.

One example of the type of chart that can be produced with PivotTables is the AsynchronousConversationPlugin. I created this chart a few years ago using WebStatistics data from the TWikiSite of my employer at the time. I emphasize that this is one example, because line, bar, area, multi-axis, and 3-dimensional charts are all available once you've got a PivotTable to work with.

Thoughts on TWikiApplications that would take advantage of PivotTable support would be any application that collects constant incremental data inputs and then allows for macro level analysis of that data.

-- KeithHelfrich - 22 Feb 2007

I don't know which path is better:

• Use ExcelImportExportPlugin to create an excel file that can later be used to create the pivot (perhaps investigate how to create the excel sheet with the pivot already created).
• Create a plugin that export into Open Document, so it can be used from Open Office and the latest Excel (again, investigate how to create the exported file with the pivot created).
• Duplicate the functionality in TWiki. To be really useable, it MUST be an ajaxified/javascripted interface that allows to quickly change the pivot. IMO, anything else will fall short.

-- RafaelAlvarez - 22 Feb 2007

I disagree that "To be really useable, it MUST be an ajaxified/javascripted interface that allows to quickly change the pivot". If you want instantaneous then use Excel. But keep javascript/ajax out of it for the collaborative stuff.. a simple edit-view-edit-view cycle is suitable.

I'd suggest duplicating functionality in TWiki is the best idea. It's job would be to:

• identify header row in table
• identify functions required

Almost it will merely be a single function that results in a table being output, dependant on a previous table. There might be a dependency in that this generated table would have to take place before the TablePlugin is called.

-- PeterPayne - 10 Oct 2007

The purpose of pivot tables is extracting information out of data. If the feedback is not "fast enough" in the edit-view-edit-view cycle, then the user will prefer to copy&paste the data in Excel. More so, the users will expect a behavior similar to Excel, so after few cycles they will ask "why not just extract the information into Excel"? I'm talking about the "perceived" "fast-enough", where a reloading of a page causes the user to think "this is slow".

Javascript/ajax was designed to improve this, so I don't see why they should be keep out "for the collaborative stuff", specially if it does make the life easier for the end-user (that's the goal, isn't it?)

-- RafaelAlvarez - 10 Oct 2007

I wonder if we can acheive the server side crux of this using some variation on ResultSets - ie, define some TOM that specifies a table in a topic, and then retrive it using a Rest request (asking for it in JSON form)

-- SvenDowideit - 29 Dec 2007

But how are "patterns and trends" computed? Did I just miss it in the above specs? Extracting data in a fine-grained and efficient way is one thing, actually compressing them in a meaningful way another. Which are the core functions to analyze the data?

-- MichaelDaum - 29 Dec 2007