How to do spreadsheet like sum of values in a table (maybe with a new syntax)?

#1

I’m creating a small “database” for a local community in TW and I would like to sum all values in a table and maybe do some operation across tiddlers with the results of operations in another one (something like when you use results in a particular sheet of a table in another sheet in programs like LibreCalc, Gnumeric or Excel).

I have been looking for information about spreadsheets in TW Links and I have found the Excel Like Table, but, despite being interesting, its syntax seems cumbersome. Also I have found Formula for TiddlyWiki (BTW the demo link doesn’t work), that is more similar to what I am looking for, but it operates on tiddler fields, instead of on table columns.

I have also seen the How to sum a data tiddler?, How make filter to sum up field values? and even a discussion about Alternatives to data tiddlers? and How the TW internal data structure looks like and why data-tiddlers are not optimal. My feeling after looking at them and trying to even think in more structured data in TW, is that simple key-value data tiddlers are “too flat” and putting, calculating and cross-referencing information via data tiddlers is cumbersome (but maybe there is a better way that I’m not aware of).

What I would like to have is something like “named tables” in tiddlers syntax like this:

| !name | !value | !month |
| Enie | 1 | Feb |
| Minie | 3 | Jan |
| Maynee | 4 | May |
| Mo | 5 | Jun |
> myTable |  This is just a longer caption associated with the table.

And be able to put in a tiddler field some kind of formula, like:

{{{ [[<currentTiddler>get<myTable#!value>sum[]] }}} to obtain 13

(with maybe a more tiddlywikish syntax for the formula).

I presume that this means a major change in TW syntax or major knowledge about how to extend it. Or, as said, maybe is a better way that doesn’t imply to spread only key-value information across several Tiddlers. Maybe the route is to make the TW parsing capabilities on its own tables available via some plugin, so it can be extended as I propose.

Which could be the route to make operations on rows and columns TW tables data available to the wiki user, with the frequently friendly and succinct syntax that characterizes TW?

Ps: having named tables would enable us to use them in internal references, like see table [[ExTiddler##myTable]] for more details.

#3

Shiraz Dynamic Tables have summary section which performs any type of math calculations on a single column! Have a look and it may give you some clue for your case!

#4

I think a tiddler should be considered the element in tiddlywiki where ever possible, why ? because its title is the key to the information in a record. Tables are just a way of presenting this information. In the same way I may generate a table we use the same “filter” to sum its fields etc… The idea of summing up the rows of a table misses the point that a table is listing tiddlers, rather than sum the table we sum the tiddlers (also presented in a tables, list, form …

There are a few circumstances where data tiddlers make sense, but even more where packing tiddlers into a JSON or Plugin is effectively the same but the can quickly be referenced as tiddlers.

A filter of records (rows) and a list of fields (call them columns if you will ) are what makes a table, and to not use the tiddlers is diverging.

I have a theory that one particular reason tiddlywiki is as successful and pliable as it is because the record is presented at “eye level” and any tiddler can be a member of many tables or lists or sets.

I also want to note I abandoned the tiddlywiki wikitext table the “|” long ago and I now use the html tables tags with filters that $list both rows and sometimes also columns “dynamically”, that is almost everything I make in tiddlywiki is in some way manipulating set of tiddlers.

A table is just a view we sometimes use. But the sets are already sitting there waiting to be revealed.

The above is my answer to this question. ask if you need it illustrated.

1 Like
#5

[…]

I agreed that the way TW makes reuse and recontextualization possible is big part of its success. But I disagree on the part of abandoning WikiText notation in favor of HTML, on the base that tables are just a way of presenting information, as we would distance ourselves from a rich tradition of light tables as a way to store information (like CSV) and even more reusable TW contexts (for example the Quick Tables examples by @Mohammad and the workflows it enables).

A more philosophical question would be related with if WikiText is just/mainly a rendering mechanism or a structured queryable language and, if is the second, which queries we want to be able to run. As TW has already parsing mechanisms for its own content in WikiText, I tend to look it in the second way and the issue would be which affordances TW provides to its users to access semi-structured information inside tiddler’s text, including tables stored information.

@Mohammad thanks for pointing me to dynamic tables. I have been looking at them and seem pretty close to what I’m looking for. In fact by reading the initial credits to TiddlyTables, I remembered that I have them in the radar to create Kaban like tables for Projectify projects data, but I totally lost track of it in the context of spreadsheet like experiences. I will experiment with both of them more. But for now, looking Dynamic table from data tiddlers macro, I was wondering if it is possible to have a JSON data tiddler like:

{
    "savingsType": "personal",
    "details": {
        "Jan08": {
            "Amount": 3,
            "Taxes": null
        },
        "Feb03": {
            "Amount": 2,
            "Taxes": "yes"
        }
    },
    "notes": "Testing nested information in JSON, that could be better represented as a table."
}

and use a similar way to create a dynamic table from Shiraz examples, but without the column being the more external keys in JSON data tiddlers (in the above example would be Jan08 and Feb03). Could I make a dynamic table saying: collect all JSON tiddlers with a particular tag, using values for data columns Amount and Taxes inside Jan08 key?

On the idea of supporting more structured data inside the tiddlers, beyond JSON and data tiddlers, I imagine that some TW extension to support application/yaml and application/toml tiddlers types, would be pretty interesing. I would make possible to have readable tiddlers storing structured/queryable data like this:

savingsType: personal
details: |
    | Date | Amount | Taxes |
    | Jan 08 | 3 | |
    | Feb 03 | 2 | yes |
notes: Just testing combining CSV data inside YAML (inside a new data tiddler type?).

My core concern is about structured multidimensional data representation, input and manipulation in TW, where key-value pairs are not enough and usually nested/tabular representations are needed. Entering nested values by hand is cumbersome (as previous YAML vs JSON examples show), but maybe TiddlyTables or Dynamic Tables may provide a proper UI for that. As said, I’ll explore more both.

I’m kind of sorry for the long post. But, sometimes when you’re a newbie (lets say in TW multidimensional data input/manipulation), the design space is broader as you still don’t know the “TW way”, and I will like to leave a record of imagined possibilities, as I learn about the current ones.

#6

Each to their own prefered method, that is a feature of Tiddlywiki.

Supporting my own argument but addressing;

It would be possible to use the “subTiddler” or structured tiddlers like the system tiddlers and expanded with the “tree macro” to get multi-dimensional representations.

tiddler/subtiddler/subtid

A well designed system will also be tolerant of “missing information” which is important in the “real world”.

1 Like