create new tag
, view all tags

Advanced TWiki Spreadsheet Formulas: Power and Flexibility - Not For Grandma

2013-03-25 - 04:14:46 by PeterThoeny in Development
TWiki's SpreadSheetPlugin just got 9 new functions that add hash capabilities to TWiki. But first some history. Spreadsheets became accessible to the masses in the eighties when Dan Bricklin and Bob Frankston created VisiCalc for the Apple II and later for PCs. They became ubiquitous with Excel; it seems like everybody is using them nowadays, at least to maintain data in tabular form.

Spreadsheets were initially developed to automate paper-based accounting worksheets. Formulas can be placed in table cells, they are evaluated dynamically, and the result is displayed based on the contents of other cells. Whenever content is changed, the formulas are reevaluated. This makes the spreadsheet useful for "what-if" analysis since many cases can be rapidly investigated without tedious manual recalculation.

TWiki has supported tables since its inception. Spreadsheet functions were introduced with the SpreadSheetPlugin in 2001. Initially just a dozen functions were available for simple table arithmetic. Over time, many more functions have been added, allowing ever more advanced computations. This blog post describes some advanced spreadsheet topics using some of the (120) current functions.

Excel confines data and formulas to be in rows and columns. In contrast, TWiki functions can be used anywhere in a TWiki page, not just in table cells. This allows users to create content that changes dynamically based on other content, or based on the logged-in user. When TWiki is used as an application platform, developers create TWiki applications using TWikiForms, HTML forms and formatted searches. Dynamic content can also be created using SpreadSheetPlugin functions, IF statements, and JavaScript.

Region: Sales:
Northeast 320
Northwest 580
South 240
Europe 610
Asia 220
Total: 1970
First, a very simple example using the tabular form as seen at the right. The "Sales" row contains regional sales numbers. The Total is calculated dynamically using %CALC{$SUM($ABOVE())}%, e.g. a sum is applied to all cells above the current cell. The total is updated automatically whenever a user updates the sales numbers. The SpreadSheetPlugin handles two variables: %CALC{...}% and %CALCULATE{...}%. The former is used for functions that reference table cells, the latter for everything else. In this blog, we focus on the latter case.

Let's look at some SpreadSheetPlugin use cases:

1. Show grand total of tables in several topics

Objective: Show the grand total of several topics containing tables.

The SpreadSheetPlugin has $SET() and $SETM() functions to set a variable for later use. Use $SET() to set a variable, and use $SETM() to update a variable based on some arithmetic. Use $GET() to retrieve previously set variables. Our objective can be solved using $SETM() and $GET():

  • In each topic, calculate the total of a table column using $SUM($ABOVE()). Then add the total to a grand total variable:
    %CALC{$SETM(grandtotal, + $SUM($ABOVE()))}%
  • Include each topic in a master topic; hide the included topics if you are only interested in the grand total.
  • Display the grand total:

See SpreadSheetRollupTest for a working example.

2. Show age of topics using background color

Objective: In a list of topics, indicate recently updated topics using a yellow background as shown in the example to the right, using darker yellow for more recent updates.

We get the list of topics with a %SEARCH{}%, and we use the format="" parameter to format each search hit. We want to set a CSS background-color that changes based on the age of the topic. Colors are specified using RGB (red green blue) hexadecimal numbers. For example, a mid-range yellow is #ffffbb, a faint yellow is #ffffee. In a search format, $date represents the last update date of the topic, such as 2013-03-24 20:36. Our goal is to use #ffffff (white) for topics older than 7 days, a #ffffbb yellow for topics updated today, and a yellow proportionally to age for topics updated in between. We are going to use the following spreadsheet functions to convert the date into a hex color. We will put these functions together later in the example.

  • $SET(today, $TODAY()) - set a variable with today's date in serialized form (for comparison)
  • $TIME($date) - convert the topic update date into serialized form (for comparison)
  • $TIMEDIFF(date1, date2, day) - compare two dates using day units
  • $ROUND(number) - round the resulting time difference, which now represents the topic age in days
  • $SET(age, number)) - store the resulting topic age
  • $IF($GET(age)<=6, then yellow color based on age) - conditional to use yellow background color; yellow is used for ages 0 to 6 days (pseudo code)
  • $INT(185+$GET(age)*10) - calculate yellow color based on age; age 0 results in decimal color 185, age 6 results in decimal color 245
  • $LOWER($DEC2HEX(decimal)) - convert the decimal to lower case hex; age 0 results in hex color b9, age 6 results in hex color f5
  • background-color: #ffff$LOWER($DEC2HEX($INT(185+$GET(age)*10))) - CSS for yellow background color using above formulas

Here is the complete example:

%CALCULATE{$SET(today, $TODAY())}%
 format="   * <span style='$percntCALCULATE{$SET(age, $ROUND($TIMEDIFF($TIME($date), $GET(today), 
   day)))$IF($GET(age)<=6, background-color: #ffff$LOWER($DEC2HEX($INT(185+$GET(age)*10))),)}$percnt'>[[$topic]]</span>"


  • A newline has been introduced in the format="" for readability. In actual use, put the whole format into one line.
  • The %CALCULATE{}% in the SEARCH format is escaped as $percntCALCULATE{}$percnt. If you do not escape the CALCULATE, it will evaluate once before the search, not once per search hit as intended.

3. Search content once and render the result in different buckets

Objective: In a feature request application you want to show features in multiple tables, organized by status.

Using a search to show a table for each status is easy, but it can be slow. The page loads faster if you search only once, and store the status, summary, date and other fields in hashes. Then you use those hashes to render each table by status.

Hashes are named sets of key & value pairs. For example, a hash called age may have first names as keys and ages as values. First let's learn how to use hashes in TWiki before we go into the feature request application.

  • $SETHASH() - clear all hashes, e.g. to start with a clean slate
  • $SETHASH(age, Jane, 26) - set the key "Jane" of hash named "age" to "26"
  • $SETMHASH(age, Jane, +10) - modify the key "Jane" by adding 10 to the existing value, resulting in a value of "36"
  • $LIST2HASH(age, Anna, 21, Zoltan, 95) - convert a list of key value pairs to a hash and add it to the hash named "age"
  • $GETHASH(age, Anna) - retrieve value of key "Anna" of hash "age", resulting in "21"
  • $GETHASH(age) - get all keys of hash "age" in list format, resulting in "Anna, Jane, Zoltan"
  • $HASH2LIST(age, $key is $value) - convert a hash to a list while formatting the output, resulting in "Anna is 21, Jane is 36, Zoltan is 95"

Now that you understand the basics of how hashes work in TWiki let's move on to the feature request application. The application uses TWikiForms and FormattedSearches as described in an earlier blog post. This example TML does the formatted search and shows proposed features by state:

 format="$percntCALCULATE{$SETHASH(status, $topic, $formfield(Status))$SETHASH(summary, $topic,
   '''$summary''')$SETHASH(date, $topic, $date)}$percnt"
---++ Proposed Features
| *Feature* | *Updated* |
%CALCULATE{$LISTJOIN($n, $LISTEACH(| [[$item]]: $GETHASH(summary, $item) | $GETHASH(date, $item) |,
  $LISTIF($EXACT($GETHASH(status, $item), Proposed), $GETHASH(status))))}%
---++ Accepted Features
| *Feature* | *Updated* |
%CALCULATE{$LISTJOIN($n, $LISTEACH(| [[$item]]: $GETHASH(summary, $item) | $GETHASH(date, $item) |,
  $LISTIF($EXACT($GETHASH(status, $item), Accepted), $GETHASH(status))))}%

As in the previous example, newlines are introduced in the format="" and the %CALCULATE{}% for readability, they need to be removed in actual use.

First we search all features and store the status, summary and date in hashes, using the topic name as the key. The summary is marked as a string using '''triple quotes''', this avoids unexpected results in case the summary contains commas or parenthesis. The SEARCH produces no output, it simply stores the result in hashes. Then we build a table that shows all proposed features, followed by a table showing all accepted features. Reading the CALCULATE formula from inside out:

  • $GETHASH(status) - get the list of all keys of the hash named "status", e.g. all topics found
  • $LISTIF($EXACT($GETHASH(status, $item), Proposed), ...) - filter the topic list and keeps only those in status "Proposed"
  • $LISTEACH(| [[$item]]: $GETHASH(summary, $item) | ... |, ...) - format each topic as a table row
  • $LISTJOIN($n, ...) - convert the comma-space list into lines separated by newlines

I hope these examples are useful. They just scratch the surface; much more is possibly with the currently 120 functions that are at your disposal. Some more examples for inspiration:

  • Query content in an external database, modify the result using spreadsheet functions, populate a table with it, and create nice looking graphs using the ChartPlugin. I have done just that in the past: At Wind River we wanted to show bug trends in team dashboards. We did so with charts showing number of bugs opened, resolved and closed over the last few month.
  • Manage paginated search. The latest TWiki does SearchResultsPagination using spreadsheet formulas.
  • Count the frequency of items, such as how many times each first name occurs in a list. $SETMHASH() has an example.

What use cases do you have? I would be delighted if you share what you have done, or what you would like to do using SpreadSheetPlugin functions.



I recently wanted a simple variable to display a bar chart for many different tables. Because the tables could have a variable number of rows and columns, I used the following code to build up a totals table so the chart could simply grab the x-axis from row 1 and the y-axis from row 2.

%CALC{$SET(vbar,|)}% %CALC{$WHILE($counter <= $LISTSIZE($LIST(R1:C2..R1:C999)),$SETHASH(totals,$T(R1:C$INT($counter+1)),$SUM(R2:C$INT($counter+1)..R999:C$INT($counter+1))))}% %TABLE{name="TotalsTable"}%
| %CALC{$LISTJOIN($GET(vbar),$HASH2LIST(totals,$key))}% |
| %CALC{$LISTJOIN($GET(vbar),$HASH2LIST(totals,$value))}% |

One problem is that I want to put this code in a variable so it can be reused, but when reused on the same page the hash contains items from the last table. I can't figure out how to clear the hash set or use a randomized name.

-- Michael Sprague - 2013-04-06

You do not need to specify the exact last row number for the ChartPlugin, any arbitrary large number will do. The ChartPlugin will use up to the actual last row.

You can clear a hash using $SETHASH(name). See SpreadSheetPlugin#FuncSETHASH.

On numbering, use either a random, such as $INT($RAND(1000)), or set a counter and increment it, such as $SET(tablenr, 0), then $SETM(tablenr, +1) before each table.

-- Peter Thoeny - 2013-04-06

Thanks. That fixes my problem with the HASH. However, if I have a table of indeterminate rows and columns, can I get a range for the last table row with R999:C1..R999:C999?

-- Michael Sprague - 2013-04-07

Michael: Use $ROW() after a table to get the number of rows of the preceding table. Use $COLUMN() after a table to get the number of columns of the preceding table. I just enhanced the plugin for the latter feature. Wave TWiki is updated as well.

-- Peter Thoeny - 2013-04-08

The SpreadSheetPlugin has been updated several times since this blog post:

  • Allow newlines and indent around functions and function parameters, which makes complex functions much more readable
  • Allow newlines in triple-quoted strings
  • Add FORMAT(CURRENY, ...) with support for currency symbol
  • Fix VALUE function bug with incorrect exponential number
  • Ability to delete SET() variable
  • Do not strip trailing spaces in SETIFEMPTY()
  • Fix plural of month to months in FORMATTIMEDIFF()
  • TRANSLATE() supporting double quote and apostrophe-quote (single quote) escape tokens
  • Add short and compact format to FORMATTIMEDIFF()

-- Peter Thoeny - 2014-10-03

The latest SpreadSheetPlugin has a RANDOMSTRING() function, can be used to generate random passwords and license keys. With this and the new ExecutePlugin you can use TWiki as the GUI for license management.

-- Peter Thoeny - 2014-10-24


Edit | Attach | Watch | Print version | History: r6 < r5 < r4 < r3 < r2 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r6 - 2013-03-27 - PeterThoeny

Twitter Delicious Facebook Digg Google Bookmarks E-mail LinkedIn Reddit StumbleUpon    
  • Help
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.