Analysis area: pivot table and several charts

The chart and the statistics

A table tab has two modes, on the Data and Analysis buttons in the bar. Data is the usual table; Analysis is a board where as many charts and pivot tables as you like live side by side over the same source — each on a card, with an editable title, a gear button that reveals the selectors and a ⋮ menu (move, duplicate, remove).

Real statistics. Besides bars, line and scatter, the chart does a box plot: one box per category with the quartiles, the median as a thick line and the whiskers at the Tukey fence (1.5 × IQR) — not at the minimum and maximum. Over the mean of a column, the Error selector adds the spread whisker: ± standard deviation (which describes the data), ± standard error or ± 95% CI (which describe how precise the mean itself is). All three appear only with the mean chosen, because "sum ± deviation" means nothing. The Log Y axis box switches to a logarithmic scale, for data spanning orders of magnitude; when some value is zero or negative it is ignored with a notice, because the logarithm does not exist there and dropping the point silently would make the chart lie. On the scatter, Trend line draws the regression line with R², fitted in the database over all the rows — not over the sampled points shown on screen. Under the drawing sits a line of descriptive statistics for the chosen column (n, mean, deviation, 95% CI, minimum, quartiles, median and maximum), which is a query of its own: deriving it from the bars would give the average of averages, which matches the real mean only when every group has the same size.

Pivot table and totals

Pivot table. Pick the fields that go on Rows and on Columns (you can nest more than one on each axis; dragging reorders the levels), the field on Value and how to reduce it: Sum, Count, Distinct count, Average, Minimum or Maximum. With no field on Value, rows are counted. It is also on the table's side mini-bar, next to the Chart, for a quick look without leaving the data.

The aggregation runs in the database and comes back already reduced — no raw row travels up, so a Parquet file with tens of millions of rows stays workable. Because the query is standard SQL, the pivot table works on every database of the SQL client (SQLite, DuckDB, PostgreSQL, MySQL, SQL Server and Oracle), and not only on DuckDB as the chart does.

Totals. The Row totals and Column totals boxes add the closing row and column. For Average the total is weighted, not the average of averages — which would be wrong whenever the groups have different sizes. A Distinct count cannot be totalled (the union of distinct sets does not follow from the sum of the parts), so there the boxes are disabled, with the reason in the tooltip, instead of showing a wrong number.

Where it is saved, CSV and grouping

Where the analysis is saved. In a JSON next to the data file (sales.parquet → sales.parquet.analysis.json): an open format that goes into Git along with the data and travels with it to a colleague. The Analysis file button opens that JSON in a tab, and emptying the analysis deletes the file. A whole folder opened as a table has no path of its own, so that analysis is not saved — the button says so. Each card also has Add to today's note: the chart goes as a mermaid block — which the note draws and which stays editable — when it has that form (bars, line, histogram and pie; scatter and box go as an image), and the pivot table as a Markdown table, which in the note remains text you can copy and edit. In the result of an SQL query the same board opens from the Analysis area button in the footer, aggregating wherever that result's chart does (the local copy or the database); there it is not saved, because a result has no file next to it — what matters goes to the note through Add to today's note.

Report and copy. Each chart's Copy button offers Copy as Mermaid, Copy the data (Markdown table) — the values the chart shows, already aggregated, with numbers right-aligned — and Copy image; whatever Mermaid cannot carry (error bars, log axis) is stated right away. In the Analysis area bar, Copy report gathers the whole analysis in Markdown — a line with the source, the filter in effect and the date, and a heading per card with the chart in Mermaid or the table — and Report to today's note sends it straight there. The Pie shows parts of a whole: it accepts only Sum and Count, draws the largest categories and adds up the rest in “Other”, saying how many were added up.

In every table. The chart, the pivot table and the Analysis area also exist in the Excel sheet, in delimited text (even unsaved), in Tasks (the Analysis view, kept inside the board), in Bases (the Analysis button, saved next to the .lnq without changing the Base) and in the Custom table (Analysis button; the password column is left out). In these screens the data lives in memory: on the first filter or the first analysis it is copied to DuckDB, and the grid filter then uses the same rule as the analysis — the chart describes exactly the rows the grid shows. The sheet sums the stored value (a cell showing “$3,750.50” sums 3750.5) and filters by the displayed text, which is what you type. When the screen loaded only part of the file (a sheet above the limit, a cut tolerant read), the analysis refuses to aggregate and says why: the sum of a part would be a wrong number.

Data quality. The table's Quality button opens, next to the grid, one card per column with what it actually holds: the bar of rows with a value, empty (empty text or only spaces) and null — counted separately, because empty is not null —, how many distinct values there are and how many appear only once; in numeric columns, mean, standard deviation, quartiles, zeros, negatives and outliers (outside the Tukey fence, the same as the boxplot), with a mini-histogram; in text columns, the length and the patterns of shape. The most frequent values are clickable: a click becomes a selection (see below), and another value of the same column replaces it. The alerts point out a column with no value, a constant one, a possible key, half or more without a value, many zeros, a highly skewed distribution and a number stored as text. The profile covers the whole set with the filter in effect — never a sample —, computed in DuckDB, up to 60 columns. The summary copies as a Markdown table or goes to today's note; and in the Analysis area the Quality button adds the same profile as a card, which goes into the report as a table (one row per column). An analysis with a quality card only opens in this version of LightNote onward.

Selections and “show as”. Clicking a bar or slice of the chart, a histogram bin, double-clicking a pivot table cell or clicking a frequent value in the Quality panel filters the table by it — and the filter becomes a selection, a tag above the grid (uf = “SP”, value ≥ 10 and < 20) with an X to remove it. Selections always combine by AND, outside the Filter panel's group: with the group combining by OR, a selection narrows the slice instead of widening it; changing the filter panel does not remove them, and another selection on the same column replaces the previous one. A selection compares the stored value — the category the chart grouped —; a histogram bin runs from its start to the start of the next one, and the last one has no upper limit. A category grouped by pattern (a pattern is not a value in the table) and the Other slice do not filter, and the chart and the pivot table say why. The Analysis area, the profile and the report caption follow the selections. In the pivot table, Show as rewrites the already aggregated values: % of grand total, % of row total, % of column total, running total and difference from previous row. Percentages and running totals only apply with Sum or Count — adding averages or minimums gives the total of nothing —, and the basis of the calculation appears above the matrix and in the report.

With a CSV, a single gesture — and a way out when the separator fools it. Open as table opens through DuckDB, with free SQL, chart, pivot table and the analysis area. Sometimes the separator is not recognised (rows with differing column counts usually cause this) and the table comes back with a single column, the whole line inside it. When that happens, a bar appears above the table saying how many columns the file seems to have and offering the tolerant read: it reads line by line without requiring equal columns — the same reading search and the AI use — and it filters, sorts and has chart, pivot table and Analysis area — only no free SQL. For text that is not saved yet, or with a delimiter you choose by hand, use View → View as delimited file….

Exported CSV opens straight in Excel. Export CSV in Tasks, in the spreadsheet and in the Custom table writes with your language’s list separator — a semicolon where the comma is the decimal mark — and with the UTF-8 mark Excel needs to read accented letters: the file opens in columns, with no import wizard. Copy as → CSV keeps the comma, which is what you paste into a script or a message; the SQL result export lets you choose the separator.

Group by pattern. In a column of dates or codes the distinct values are almost as many as the rows, and grouping by them says nothing. The pattern mask turns every letter into A and every digit into N, leaving the rest: 2027/10/10 becomes NNNN/NN/NN. Millions of rows then collapse into two or three shapes — and whatever escapes them is exactly the dirty data (the date that came as 10-10-2027, the code without its prefix). In the chart it is the Pattern checkbox next to the X axis; in the pivot table, right-click a field's pill (it is per field, so you can cross the shape of a code with a normal dimension); and in the Quality panel the most common patterns appear under the most common values of each column. The work runs in the database. On SQLite and generic ODBC the option is disabled with the reason: the text function the mask relies on is missing.