Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetData/Tests/SheetDataTests/PivotTests.swift10// Module : SheetDataTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetData9192/// Pivot results against cubes computed with pandas groupby at every (row prefix, column prefix) level.93@Suite struct PivotTests {94 let e: DataEngine9596 init() throws { e = try Fixtures.engine() }9798 private func compute(_ spec: PivotSpec) throws -> PivotResult { try PivotEngine.compute(spec, workbook: nil, data: e) }99 private func regionByCategory(_ value: PivotValue) -> PivotSpec {100 PivotSpec(source: .dataset("sales_r"), rows: [PivotField("region")], columns: [PivotField("category")], values: [value])101 }102103 @Test func everyAggregationMatchesPandas() throws {104 for aggregation in PivotAggregation.allCases where aggregation != .countNumbers {105 let field = aggregation == .distinctCount ? "customer_id" : (aggregation == .product ? "discount1" : "quantity")106 let r = try compute(regionByCategory(PivotValue(field, aggregation)))107 expectMap(pivotMap(r), equals: Fixtures.pivot("agg_\(aggregation.rawValue)"), aggregation.rawValue)108 }109 // count vs countNumbers on a text column holding some numbers110 try e.execute("CREATE TABLE mixed AS SELECT * FROM (VALUES ('a', '1'), ('a', 'x'), ('a', NULL), ('b', '2.5'), ('b', '3')) t(k, v)")111 let spec = PivotSpec(source: .dataset("mixed"), rows: [PivotField("k")], values: [PivotValue("v", .count), PivotValue("v", .countNumbers)])112 let r = try compute(spec)113 #expect(r.valueNames == ["Count of v", "Count Numbers of v"])114 #expect((0..<3).map { [r.value(row: $0, column: 0), r.value(row: $0, column: 1)] } == [[2, 1], [2, 2], [4, 3]])115 }116117 @Test func nestedFieldsSubtotalsAndGrandTotals() throws {118 let spec = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("region"), PivotField("category")],119 columns: [PivotField("order_date", grouping: .datePart(.year)), PivotField("is_online")], values: [PivotValue("revenue", .sum)])120 let r = try compute(spec)121 expectMap(pivotMap(r), equals: Fixtures.pivot("two_by_two"), "two_by_two")122 // 5 regions (incl. blank) × 5 categories + 5 subtotals + grand total; 2 years × 2 + 2 subtotals + grand total123 #expect(r.rowItems.count == 31 && r.columnItems.count == 7)124 #expect(r.rowItems.last?.kind == .grandTotal && r.columnItems.last?.kind == .grandTotal)125 #expect(r.rowItems.filter { $0.kind == .subtotal }.count == 5)126 #expect(r.rowItems.first?.labels == ["East", "Garden"])127 #expect(r.rowItems[r.rowItems.count - 2].labels == ["(blank)"]) // blank region sorts last, its subtotal just before the grand total128 // the engine returned only the non-empty groups, far fewer than a cross product of raw values129 #expect(r.groupCount <= 31 * 7)130 // without subtotals and totals only the leaves remain131 var plain = spec132 plain.showSubtotals = false; plain.showRowGrandTotals = false; plain.showColumnGrandTotals = false133 let p = try compute(plain)134 #expect(p.rowItems.count == 25 && p.columnItems.count == 4)135 #expect(p.rowItems.allSatisfy { $0.kind == .item } && p.columnItems.allSatisfy { $0.kind == .item })136 let leaves = Fixtures.pivot("two_by_two").filter { $0.key.split(separator: "\u{a6}").count == 3 }137 expectMap(pivotMap(p), equals: leaves, "leaves only")138 }139140 @Test func dateAndNumericGrouping() throws {141 let dates = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("order_date", grouping: .datePart(.year)), PivotField("order_date", grouping: .datePart(.quarter))],142 columns: [PivotField("order_date", grouping: .datePart(.month))], values: [PivotValue("quantity", .sum)])143 let r = try compute(dates)144 expectMap(pivotMap(r), equals: Fixtures.pivot("date_parts"), "date_parts")145 #expect(r.rowItems.first?.labels == ["2024", "Q1"])146 #expect(r.columnItems.prefix(3).map { $0.labels[0] } == ["Jan", "Feb", "Mar"])147 #expect(r.rowFields == ["order_date (Year)", "order_date (Quarter)"])148 let bins = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("unit_price", grouping: .bin(start: 0, size: 100))], columns: [PivotField("region")], values: [PivotValue("quantity", .sum)])149 let b = try compute(bins)150 expectMap(pivotMap(b), equals: Fixtures.pivot("bins"), "bins")151 #expect(b.rowItems.map { $0.labels.first ?? "" } == ["0 – 100", "100 – 200", "200 – 300", "300 – 400", ""])152 let day = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("order_date", grouping: .datePart(.day))], values: [PivotValue("order_id", .count)])153 #expect(try compute(day).rowItems.count == 32)154 }155156 @Test func degenerateShapes() throws {157 let noColumns = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("category"), PivotField("product")], values: [PivotValue("revenue", .average)])158 let a = try compute(noColumns)159 expectMap(pivotMap(a), equals: Fixtures.pivot("no_columns"), "no_columns")160 #expect(a.columnItems.count == 1)161 let noRows = PivotSpec(source: .dataset("sales_r"), columns: [PivotField("region")], values: [PivotValue("quantity", .sum)])162 let b = try compute(noRows)163 expectMap(pivotMap(b), equals: Fixtures.pivot("no_rows"), "no_rows")164 #expect(b.rowItems.count == 1)165 let single = try compute(PivotSpec(source: .dataset("sales_r"), values: [PivotValue("quantity", .sum), PivotValue("order_id", .count)]))166 #expect(single.rowItems.count == 1 && single.columnItems.count == 1)167 #expect(single.value(row: 0, column: 0) == Fixtures.pivot("agg_sum")["||"])168 #expect(single.value(row: 0, column: 1) == 2000)169 // an empty source gives an empty table, not an error170 try e.execute("CREATE TABLE empty_src AS SELECT * FROM sales_r WHERE false")171 let empty = try compute(PivotSpec(source: .dataset("empty_src"), rows: [PivotField("region")], columns: [PivotField("category")], values: [PivotValue("quantity", .sum)]))172 #expect(empty.rowItems.map(\.kind) == [.grandTotal] && empty.value(row: 0, column: 0) == nil)173 #expect(throws: DataError.self) { _ = try compute(PivotSpec(source: .dataset("sales_r"), rows: [PivotField("nope")], values: [PivotValue("quantity")])) }174 #expect(throws: DataError.self) { _ = try compute(PivotSpec(source: .dataset("sales_r"), rows: [PivotField("region")], values: [])) }175 #expect(throws: DataError.self) { _ = try compute(PivotSpec(source: .dataset("nope"), values: [PivotValue("x")])) }176 }177178 @Test func filters() throws {179 var spec = regionByCategory(PivotValue("quantity", .sum))180 spec.filters = [PivotFilter("region", .include(["North", "West"])), PivotFilter("category", .exclude(["Toys"])), PivotFilter("unit_price", .condition(.greaterOrEqual, [50]))]181 expectMap(pivotMap(try compute(spec)), equals: Fixtures.pivot("filtered"), "filtered")182 // top 3 products by revenue, sorted by value: totals reflect the kept items only183 let top = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("product", sort: .value(index: 0, ascending: false))], columns: [PivotField("region")],184 values: [PivotValue("revenue", .sum)], filters: [PivotFilter("product", .top(count: 3, valueIndex: 0, bottom: false))])185 let r = try compute(top)186 expectMap(pivotMap(r), equals: Fixtures.pivot("top3"), "top3")187 #expect(r.rowItems.filter { $0.kind == .item }.map { $0.labels[0] } == Fixtures.strings("top3_order"))188 // filter on a grouped item (the year), including NULL handling in exclude189 var year = regionByCategory(PivotValue("order_id", .count))190 year.filters = [PivotFilter("order_date", grouping: .datePart(.year), .include([2025])), PivotFilter("region", .exclude([.null]))]191 let y = try compute(year)192 let want = try e.query("SELECT count(*) FROM sales WHERE year(order_date) = 2025 AND region IS NOT NULL").value(row: 0, column: 0).doubleValue193 #expect(y.value(row: y.rowItems.count - 1, column: y.dataColumnCount - 1) == want)194 #expect(!y.rowItems.contains { $0.labels == ["(blank)"] })195 var badTop = regionByCategory(PivotValue("quantity", .sum))196 badTop.filters = [PivotFilter("product", .top(count: 3, valueIndex: 0, bottom: false))]197 #expect(throws: DataError.self) { _ = try compute(badTop) }198 }199200 @Test func calculatedFieldsAggregateBeforeDividing() throws {201 var spec = regionByCategory(PivotValue("avg_price"))202 spec.calculatedFields = [PivotCalculatedField(name: "avg_price", expression: "revenue / quantity")]203 let r = try compute(spec)204 expectMap(pivotMap(r), equals: Fixtures.pivot("calculated"), "calculated")205 #expect(r.valueNames == ["avg_price"])206 // quoted field names, functions and constants; mixed with a plain value207 spec.calculatedFields = [PivotCalculatedField(name: "avg_price", expression: "round(\"revenue\" / nullif(QUANTITY, 0), 6) * 1")]208 spec.values.append(PivotValue("revenue", .sum))209 let q = try compute(spec)210 let want = Fixtures.pivot("calculated")211 for (k, v) in pivotMap(q) { #expect(abs(v - want[k]!) < 1e-6) }212 // the grand total is the ratio of the totals, not the sum of the cell ratios213 let cells = pivotMap(r).filter { !$0.key.hasPrefix("||") && !$0.key.hasSuffix("||") }.values.reduce(0, +)214 #expect(abs(cells - want["||"]!) > 1)215 for bad in ["1 + 1", "revenue); DROP TABLE sales_r; --", "nope / quantity is not a field \"nope\""] {216 spec.calculatedFields = [PivotCalculatedField(name: "avg_price", expression: bad)]217 #expect(throws: DataError.self, "\(bad)") { _ = try compute(spec) }218 }219 #expect(try e.rowCount(of: "sales_r") == 2000)220 }221222 @Test func showAsPercentages() throws {223 expectMap(pivotMap(try compute(regionByCategory(PivotValue("quantity", .sum, showAs: .percentOfGrandTotal)))), equals: Fixtures.pivot("pct_grand"), "pct_grand")224 expectMap(pivotMap(try compute(regionByCategory(PivotValue("quantity", .sum, showAs: .percentOfRowTotal)))), equals: Fixtures.pivot("pct_row"), "pct_row")225 let col = try compute(regionByCategory(PivotValue("quantity", .sum, showAs: .percentOfColumnTotal)))226 expectMap(pivotMap(col), equals: Fixtures.pivot("pct_col"), "pct_col")227 // the plain aggregates stay available next to the shown values228 expectMap(pivotMap(col, raw: true), equals: Fixtures.pivot("agg_sum"), "raw")229 // several values with different modes in one pass230 let both = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("region")], columns: [PivotField("category")],231 values: [PivotValue("quantity", .sum), PivotValue("quantity", .sum, showAs: .percentOfGrandTotal, name: "share")])232 let r = try compute(both)233 expectMap(pivotMap(r, value: 0), equals: Fixtures.pivot("agg_sum"), "v0")234 expectMap(pivotMap(r, value: 1), equals: Fixtures.pivot("pct_grand"), "v1")235 #expect(r.valueNames == ["Sum of quantity", "share"])236 }237238 @Test func showAsRunningTotalAndDifferences() throws {239 func spec(_ showAs: PivotShowAs) -> PivotSpec {240 PivotSpec(source: .dataset("sales_r"), rows: [PivotField("order_date", grouping: .datePart(.year))],241 columns: [PivotField("order_date", grouping: .datePart(.month))], values: [PivotValue("quantity", .sum, showAs: showAs)])242 }243 let month = "order_date (Month)"244 let running = try compute(spec(.runningTotal(field: month)))245 expectMap(pivotMap(running), equals: Fixtures.map("showas_running"), "running", subset: true)246 // December's running total equals the row total, which keeps its plain value247 let total = running.columnItems.count - 1248 #expect(close(running.value(row: 0, column: 11)!, running.value(row: 0, column: total)!))249 let diff = pivotMap(try compute(spec(.differenceFrom(field: month, base: .previous))))250 expectMap(diff, equals: Fixtures.map("showas_diff_prev"), "diff", subset: true)251 #expect(diff["2024||1"] == nil && diff["2025||1"] == nil) // no previous item: blank252 expectMap(pivotMap(try compute(spec(.percentDifferenceFrom(field: month, base: .previous)))), equals: Fixtures.map("showas_pctdiff_prev"), "pctdiff", subset: true)253 let fromJan = pivotMap(try compute(spec(.differenceFrom(field: month, base: .item(1)))))254 expectMap(fromJan, equals: Fixtures.map("showas_diff_item"), "diff item", subset: true)255 #expect(fromJan["2024||1"] == nil) // the base item itself: blank256 // along a row field: running total down the years, per month column257 let down = try compute(spec(.runningTotal(field: "order_date (Year)")))258 let raw = pivotMap(down, raw: true), shown = pivotMap(down)259 #expect(close(shown["2025||3"]!, raw["2024||3"]! + raw["2025||3"]!))260 #expect(shown["2024||3"] == raw["2024||3"])261 #expect(throws: DataError.self) { _ = try compute(spec(.runningTotal(field: "region"))) }262 }263264 @Test func sorting() throws {265 let byValue = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("category", sort: .value(index: 0, ascending: false))], values: [PivotValue("revenue", .sum)])266 #expect(try compute(byValue).rowItems.dropLast().map { $0.labels[0] } == Fixtures.strings("sort_by_value_desc"))267 let ascending = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("category", sort: .value(index: 0, ascending: true))], values: [PivotValue("revenue", .sum)])268 #expect(try compute(ascending).rowItems.dropLast().map { $0.labels[0] } == Fixtures.strings("sort_by_value_desc").reversed())269 let byLabel = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("region")], columns: [PivotField("category", sort: .label(ascending: false))], values: [PivotValue("revenue", .sum)])270 let r = try compute(byLabel)271 #expect(r.columnItems.dropLast().map { $0.labels[0] } == Fixtures.strings("sort_label_desc"))272 #expect(r.rowItems.dropLast().map { $0.labels[0] } == ["East", "North", "South", "West", "(blank)"])273 }274275 @Test func drillDownReturnsTheUnderlyingRecords() throws {276 let spec = regionByCategory(PivotValue("quantity", .sum))277 let r = try compute(spec)278 func ids(_ row: Int, _ col: Int) throws -> [Double] {279 let records = try PivotEngine.records(try r.drillDown(row: row, col: col), data: e)280 #expect(records.columnNames == (try e.schema(of: "sales_r").map(\.name))) // whole source records281 return (0..<records.rowCount).map { records.column("order_id")!.double($0)! }.sorted()282 }283 let south = r.rowIndex(keys: ["South"])!, garden = r.columnIndex(keys: ["Garden"])!284 #expect(try ids(south, garden) == Fixtures.numbers("drill_cell"))285 // the cell value is the aggregate of exactly those records286 let records = try PivotEngine.records(try r.drillDown(row: south, col: garden), data: e)287 let sum = (0..<records.rowCount).compactMap { records.column("quantity")!.double($0) }.reduce(0, +)288 #expect(sum == r.value(row: south, column: garden))289 #expect(try ids(r.rowIndex(keys: ["West"])!, r.columnIndex(keys: [])!) == Fixtures.numbers("drill_row_total"))290 #expect(try ids(r.rowIndex(keys: [.null])!, r.columnIndex(keys: ["Toys"])!) == Fixtures.numbers("drill_null_region"))291 #expect(Double(try ids(r.rowIndex(keys: [])!, r.columnIndex(keys: [])!).count) == (Fixtures.expected["drill_grand"] as! NSNumber).doubleValue)292 // grouped fields drill through the grouping expression293 let dates = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("order_date", grouping: .datePart(.year)), PivotField("order_date", grouping: .datePart(.quarter))],294 columns: [PivotField("order_date", grouping: .datePart(.month))], values: [PivotValue("quantity", .sum)])295 let d = try compute(dates)296 let q = try d.drillDown(row: d.rowIndex(keys: [2025, 2])!, col: d.columnIndex(keys: [5])!)297 let got = try PivotEngine.records(q, data: e)298 #expect((0..<got.rowCount).map { got.column("order_id")!.double($0)! }.sorted() == Fixtures.numbers("drill_date_part"))299 #expect(q.parameters == [2025, 2, 5]) // keys are bound, never spliced into the SQL300 // filters (including top-N) restrict the drilled records too301 let top = PivotSpec(source: .dataset("sales_r"), rows: [PivotField("product")], values: [PivotValue("revenue", .sum)],302 filters: [PivotFilter("product", .top(count: 3, valueIndex: 0, bottom: false)), PivotFilter("region", .include(["North"]))])303 let t = try compute(top)304 let all = try PivotEngine.records(try t.drillDown(row: t.rowItems.count - 1, col: 0), data: e)305 let wanted = try e.query("SELECT count(*) FROM sales_r WHERE region = 'North' AND product IN (SELECT product FROM sales_r WHERE region = 'North' GROUP BY 1 ORDER BY sum(revenue) DESC LIMIT 3)")306 #expect(Double(all.rowCount) == wanted.value(row: 0, column: 0).doubleValue)307 #expect(throws: DataError.self) { _ = try r.drillDown(row: 999, col: 0) }308 #expect(try PivotEngine.records(try r.drillDown(row: south, col: garden), data: e, limit: 5).rowCount == 5)309 }310311 @Test func renderTabularAndCompact() throws {312 try e.execute("""313 CREATE TABLE tiny AS SELECT * FROM (VALUES ('N', 'a', 2024, 1.0), ('N', 'a', 2025, 2.0), ('N', 'b', 2024, 4.0),314 ('S', 'a', 2025, 8.0), ('S', 'c', 2024, 16.0)) t(region, item, yr, amount)315 """)316 var spec = PivotSpec(source: .dataset("tiny"), rows: [PivotField("region"), PivotField("item")], columns: [PivotField("yr")], values: [PivotValue("amount", .sum, numberFormat: "#,##0.00")])317 let r = try compute(spec)318 let grid = PivotEngine.renderGrid(r, spec: spec)319 let n = Value.number, t = Value.text320 #expect(grid.cells == [321 [t("region"), t("item"), t("2024"), t("2025"), t("Grand Total")],322 [t("N"), t("a"), n(1), n(2), n(3)],323 [.blank, t("b"), n(4), .blank, n(4)],324 [t("N Total"), .blank, n(5), n(2), n(7)],325 [t("S"), t("a"), .blank, n(8), n(8)],326 [.blank, t("c"), n(16), .blank, n(16)],327 [t("S Total"), .blank, n(16), n(8), n(24)],328 [t("Grand Total"), .blank, n(21), n(10), n(31)],329 ])330 #expect(PivotEngine.render(r, spec: spec) == grid.cells)331 #expect(grid.headerRows == 1 && grid.headerColumns == 2)332 #expect(grid.roles[0] == [.corner, .corner, .columnHeader, .columnHeader, .grandTotal])333 #expect(grid.roles[1] == [.rowHeader, .rowHeader, .value, .value, .grandTotal])334 #expect(grid.roles[3].allSatisfy { $0 == .subtotal } && grid.roles[7].allSatisfy { $0 == .grandTotal })335 #expect(grid.columnFormats == [nil, nil, "#,##0.00", "#,##0.00", "#,##0.00"])336 // grid coordinates map back to drill-down coordinates337 let at = grid.dataCoordinates(gridRow: 2, gridColumn: 2)!338 #expect(try PivotEngine.records(try r.drillDown(row: at.row, col: at.col), data: e).rows == [["N", "b", .int(2024), .decimal("4.0")]])339 #expect(grid.dataCoordinates(gridRow: 0, gridColumn: 3) == nil)340 // compact: one label column, group rows carry the subtotals341 spec.layout = .compact342 let compact = PivotEngine.renderGrid(try compute(spec), spec: spec)343 #expect(compact.cells == [344 [t("region / item"), t("2024"), t("2025"), t("Grand Total")],345 [t("N"), n(5), n(2), n(7)],346 [t(" a"), n(1), n(2), n(3)],347 [t(" b"), n(4), .blank, n(4)],348 [t("S"), n(16), n(8), n(24)],349 [t(" a"), .blank, n(8), n(8)],350 [t(" c"), n(16), .blank, n(16)],351 [t("Grand Total"), n(21), n(10), n(31)],352 ])353 spec.showSubtotals = false354 let noSub = PivotEngine.renderGrid(try compute(spec), spec: spec)355 #expect(noSub.cells[1] == [t("N"), .blank, .blank, .blank])356 // several values and two column fields: value names get their own header row, subtotal columns appear357 let wide = PivotSpec(source: .dataset("tiny"), rows: [PivotField("region")], columns: [PivotField("yr"), PivotField("item")],358 values: [PivotValue("amount", .sum, name: "Σ"), PivotValue("amount", .count, showAs: .percentOfGrandTotal, name: "n%")])359 let w = PivotEngine.renderGrid(try compute(wide), spec: wide)360 #expect(w.headerRows == 3)361 #expect(w.cells[0] == [.blank, t("2024"), .blank, .blank, .blank, .blank, .blank, t("2024 Total"), .blank, t("2025"), .blank, t("2025 Total"), .blank, t("Grand Total"), .blank])362 #expect(w.cells[1] == [.blank, t("a"), .blank, t("b"), .blank, t("c"), .blank, .blank, .blank, t("a"), .blank, .blank, .blank, .blank, .blank])363 #expect(w.cells[2] == [t("region")] + Array(repeating: [t("Σ"), t("n%")], count: 7).flatMap { $0 })364 #expect(w.cells[3] == [t("N"), n(1), n(0.2), n(4), n(0.2), .blank, .blank, n(5), n(0.4), n(2), n(0.2), n(2), n(0.2), n(7), n(0.6)])365 #expect(w.columnFormats[2] == "0.00%" && w.columnFormats[1] == nil)366 }367368 @Test func specIsStoredAsSheetObjectPayload() throws {369 var spec = PivotSpec(name: "Sales pivot", source: .range(sheetID: 3, range: RangeRef(r0: 0, c0: 0, r1: 99, c1: 5), hasHeader: true, dateColumns: [2]),370 rows: [PivotField("region", sort: .value(index: 1, ascending: false), caption: "Région"), PivotField("d", grouping: .datePart(.quarter))],371 columns: [PivotField("price", grouping: .bin(start: 0, size: 50))],372 values: PivotAggregation.allCases.map { PivotValue("amount", $0) },373 filters: [PivotFilter("region", .include(["a", .null])), PivotFilter("d", grouping: .datePart(.year), .exclude([2020])),374 PivotFilter("amount", .condition(.between, [1, 2])), PivotFilter("region", .top(count: 5, valueIndex: 0, bottom: true))],375 calculatedFields: [PivotCalculatedField(name: "ratio", expression: "amount / price")], layout: .compact)376 spec.values += [PivotValue("amount", showAs: .runningTotal(field: "region")), PivotValue("amount", showAs: .differenceFrom(field: "d", base: .item(.string("x")))),377 PivotValue("amount", showAs: .percentDifferenceFrom(field: "d", base: .previous)), PivotValue("ratio", showAs: .percentOfRowTotal, numberFormat: "0.0%", name: "R")]378 let object = SheetObject(kind: PivotSpec.objectKind, name: spec.name, x: 0, y: 0, width: 400, height: 300, payload: try spec.payload())379 #expect(object.kind == "pivot")380 #expect(try PivotSpec(payload: object.payload) == spec)381 }382}383