SPB Git

Repository Public

PivotTests.swift 26.2 KB 383 lines swift

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
Files main
Menu

Theme