SID-01449: Sum across Tables on different Topics

Status:	Answered	TWiki version:	5.0.2	Perl version:
Category:	SpreadSheetPlugin	Server OS:		Last update:	14 years ago

Hi,

This is a very specific use case that I can't seem to find any answers for.

If I have the same table (same columns/same rows/diff data) on different topics, is there a way to sum up specific cells across multiple topics?

For example:
Topic 1("Bananas") has Table:
Banana Count -> 3
Topic 2 ("Apples") has Table:
Apple Count -> 4

If I make a new topic ("Fruits"), how do I make a new table that combines the 2? Fruit Count -> 7

I don't mind if the table has the same table structure and everything too.

-- TWikiGuest - 2012-04-16

Discussion and Answer

Two options:

1. INCLUDE those topics, and apply the CALC over all topics. Example at SpreadSheetRollupTest

2. Do a SEARCH that identifies all topics with the tables. Sum up the total using a spreadsheet variable. Untested code using TWikiForms field named "Count" for easy query (instead of table) :

%CALC{$SET(total, 0)}%
%SEARCH{
  "string that identifies your fruit topics"
  nonoise="on"
  format="$percntCALC{$SETM(total, +$formfield(Count))}$percnt"
  separator=" "
}%
Total:
%CALC{$GET(total)}%

If you have a table instead of a TWiki form you need to apply a regular expression to get the number. Assuming your table looks like:

  multiple="on"
  format="$percntCALC{$SETM(total, +$pattern(^\| Count: \| *([0-9]+) *\|.*))}$percnt"

See also VarSEARCH, QuerySearch, SpreadSheetPlugin, RegularExpression, How to Create a TWiki Application

-- PeterThoeny - 2012-04-16

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.