Repository: squashql/squashql
Pull request #267 (2024-02-15 07:32:11)

New hierarchical measure: grand total along ancestors
See https://github.com/squashql/squashql/pull/267/files#diff-10aa48f93a7e5c014cae3311fec3090dd09e74e8da1cf43536df9ab21969c3f3


Patch:
diff --git a/core/src/main/java/io/squashql/query/ComparisonMeasureReferencePosition.java b/core/src/main/java/io/squashql/query/ComparisonMeasureReferencePosition.java
index a49082a..f6177eb 100644
--- a/core/src/main/java/io/squashql/query/ComparisonMeasureReferencePosition.java
+++ b/core/src/main/java/io/squashql/query/ComparisonMeasureReferencePosition.java
@@ -25,13 +25,14 @@ public class ComparisonMeasureReferencePosition implements Measure {
   public Map<Field, String> referencePosition;
   public Period period;
   public List<Field> ancestors;
+  public boolean grandTotalAlongAncestors;
 
   public ComparisonMeasureReferencePosition(@NonNull String alias,
                                             @NonNull ComparisonMethod comparisonMethod,
                                             @NonNull Measure measure,
                                             @NonNull Map<Field, String> referencePosition,
                                             @NonNull Period period) {
-    this(alias, comparisonMethod, null, measure, referencePosition, period, null, null);
+    this(alias, comparisonMethod, null, measure, referencePosition, period, null, null, false);
   }
 
   public ComparisonMeasureReferencePosition(@NonNull String alias,
@@ -39,14 +40,22 @@ public class ComparisonMeasureReferencePosition implements Measure {
                                             @NonNull Measure measure,
                                             @NonNull Map<Field, String> referencePosition,
                                             @NonNull ColumnSetKey columnSetKey) {
-    this(alias, comparisonMethod, null, measure, referencePosition, null, columnSetKey, null);
+    this(alias, comparisonMethod, null, measure, referencePosition, null, columnSetKey, null, false);
   }
 
   public ComparisonMeasureReferencePosition(@NonNull String alias,
                                             @NonNull ComparisonMethod comparisonMethod,
                                             @NonNull Measure measure,
                                             @NonNull List<Field> ancestors) {
-    this(alias, comparisonMethod, null, measure, null, null, null, ancestors);
+    this(alias, comparisonMethod, null, measure, null, null, null, ancestors, false);
+  }
+
+  public ComparisonMeasureReferencePosition(@NonNull String alias,
+                                            @NonNull ComparisonMethod comparisonMethod,
+                                            @NonNull Measure measure,
+                                            @NonNull List<Field> ancestors,
+                                            boolean grandTotalAlongAncestors) {
+    this(alias, comparisonMethod, null, measure, null, null, null, ancestors, grandTotalAlongAncestors);
   }
 
   /**
@@ -56,7 +65,7 @@ public class ComparisonMeasureReferencePosition implements Measure {
                                             @NonNull BiFunction<Object, Object, Object> comparisonOperator,
                                             @NonNull Measure measure,
                                             @NonNull List<Field> ancestors) {
-    this(alias, null, comparisonOperator, measure, null, null, null, ancestors);
+    this(alias, null, comparisonOperator, measure, null, null, null, ancestors, false);
   }
 
   private ComparisonMeasureReferencePosition(String alias,
@@ -66,7 +75,8 @@ public class ComparisonMeasureReferencePosition implements Measure {
                                              Map<Field, String> referencePosition,
                                              Period period,
                                              ColumnSetKey columnSetKey,
-                                             List<Field> ancestors) {
+                                             List<Field> ancestors,
+                                             boolean grandTotalAlongAncestors) {
     this.alias = alias;
     this.comparisonMethod = comparisonMethod;
     this.comparisonOperator = comparisonOperator;
@@ -75,6 +85,7 @@ public class ComparisonMeasureReferencePosition implements Measure {
     this.period = period;
     this.columnSetKey = columnSetKey;
     this.ancestors = ancestors;
+    this.grandTotalAlongAncestors = grandTotalAlongAncestors;
   }
 
   @Override
diff --git a/core/src/main/java/io/squashql/query/ParentComparisonExecutor.java b/core/src/main/java/io/squashql/query/ParentComparisonExecutor.java
index aecd44d..f426d17 100644
--- a/core/src/main/java/io/squashql/query/ParentComparisonExecutor.java
+++ b/core/src/main/java/io/squashql/query/ParentComparisonExecutor.java
@@ -23,18 +23,12 @@ public class ParentComparisonExecutor extends AComparisonExecutor {
   protected BiPredicate<Object[], Header[]> createShiftProcedure(CompiledComparisonMeasure cm, ObjectIntMap<String> indexByColumn) {
     List<TypedField> ancestors = new ArrayList<>(this.pcm.ancestors());
     Collections.reverse(ancestors);
-    return new ShiftProcedure(ancestors, indexByColumn);
+    return new AncestorShiftProcedure(ancestors, indexByColumn, cm.grandTotalAlongAncestors());
   }
 
-  static class ShiftProcedure implements BiPredicate<Object[], Header[]> {
-
-    final List<TypedField> ancestors;
-    final ObjectIntMap<String> indexByColumn;
-
-    ShiftProcedure(List<TypedField> ancestors, ObjectIntMap<String> indexByColumn) {
-      this.ancestors = ancestors;
-      this.indexByColumn = indexByColumn;
-    }
+  record AncestorShiftProcedure(List<TypedField> ancestors,
+                                ObjectIntMap<String> indexByColumn,
+                                boolean grandTotalAlongAncestors) implements BiPredicate<Object[], Header[]> {
 
     @Override
     public boolean test(Object[] row, Header[] headers) {
@@ -45,7 +39,9 @@ public class ParentComparisonExecutor extends AComparisonExecutor {
           int index = this.indexByColumn.get(name);
           if (!SQLTranslator.TOTAL_CELL.equals(row[index])) {
             row[index] = SQLTranslator.TOTAL_CELL;
-            break;
+            if (!this.grandTotalAlongAncestors) {
+              break;
+            }
           }
         }
       }
diff --git a/core/src/main/java/io/squashql/query/QueryResolver.java b/core/src/main/java/io/squashql/query/QueryResolver.java
index 253aee0..922c2ac 100644
--- a/core/src/main/java/io/squashql/query/QueryResolver.java
+++ b/core/src/main/java/io/squashql/query/QueryResolver.java
@@ -348,7 +348,8 @@ public class QueryResolver {
             m.referencePosition == null ? null : m.referencePosition.entrySet().stream().collect(Collectors.toMap(e -> resolveField(e.getKey()), Map.Entry::getValue)),
             compilePeriod(m.period),
             m.columnSetKey,
-            m.ancestors == null ? null : m.ancestors.stream().map(this::resolveField).collect(Collectors.toList()));
+            m.ancestors == null ? null : m.ancestors.stream().map(this::resolveField).collect(Collectors.toList()),
+            m.grandTotalAlongAncestors);
   }
 
   private CompiledPeriod compilePeriod(Period period) {
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledComparisonMeasure.java b/core/src/main/java/io/squashql/query/compiled/CompiledComparisonMeasure.java
index 7ec1ba8..6c6bf82 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledComparisonMeasure.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledComparisonMeasure.java
@@ -16,7 +16,8 @@ public record CompiledComparisonMeasure(String alias,
                                         Map<TypedField, String> referencePosition,
                                         CompiledPeriod period,
                                         ColumnSetKey columnSetKey,
-                                        List<TypedField> ancestors) implements CompiledMeasure {
+                                        List<TypedField> ancestors,
+                                        boolean grandTotalAlongAncestors) implements CompiledMeasure {
 
 
   @Override
diff --git a/documentation/QUERY.md b/documentation/QUERY.md
index 4ade79b..12ec97e 100644
--- a/documentation/QUERY.md
+++ b/documentation/QUERY.md
@@ -911,40 +911,44 @@ const populationTable = new PopulationTable()
 
 const pop = sum("pop", populationTable.population)
 const ancestors = [populationTable.continent, populationTable.country, populationTable.city]
-const ratio = comparisonMeasureWithParent("ratio", ComparisonMethod.DIVIDE, pop, ancestors)
+const parentRatio = comparisonMeasureWithParent("parent ratio", ComparisonMethod.DIVIDE, pop, ancestors)
+const gtRatio = comparisonMeasureWithGrandTotalAlongAncestors("gt ratio", ComparisonMethod.DIVIDE, pop, ancestors)
 const query = from(populationTable._name)
-        .select([populationTable.continent, populationTable.country, populationTable.city], [], [pop, ratio])
+        .select([populationTable.continent, populationTable.country, populationTable.city], [], [pop, parentRatio, gtRatio])
         .rollup([populationTable.continent, populationTable.country, populationTable.city])
         .build()
 ```
 
 `comparisonMeasureWithParent` method is used to create a special measure built to compare values of an underlying
-measure (third argument) with the parent values of the same measure. Parenthood is indicated with the array of `ancestors`
-(fourth argument) which contains column names in "lineage reverse order". 
+measure (third argument) with the parent values of the same measure. 
 
+`comparisonMeasureWithGrandTotalAlongAncestors` method is used to create a special measure built to compare values of an underlying
+measure (third argument) with the grand total values of the same measure along the ancestors.
+
+Parenthood is indicated with the array of `ancestors` (fourth argument) which contains column names in "lineage reverse order".
 Note the columns used to define the ancestors need to be passed to the select method but not necessary to the rollup method. 
 
 Result
 ```
-+-------------+-------------+-------------+------------+---------------------+
-|   continent |     country |        city |        pop |               ratio |
-+-------------+-------------+-------------+------------+---------------------+
-| Grand Total | Grand Total | Grand Total |       28.5 |                 1.0 |
-|          am |       Total |       Total |       17.0 |  0.5964912280701754 |
-|          am |      canada |       Total |        6.0 | 0.35294117647058826 |
-|          am |      canada |    montreal |        2.0 |  0.3333333333333333 |
-|          am |      canada |       otawa |        1.0 | 0.16666666666666666 |
-|          am |      canada |     toronto |        3.0 |                 0.5 |
-|          am |         usa |       Total |       11.0 |  0.6470588235294118 |
-|          am |         usa |     chicago |        3.0 |  0.2727272727272727 |
-|          am |         usa |         nyc |        8.0 |  0.7272727272727273 |
-|          eu |       Total |       Total |       11.5 | 0.40350877192982454 |
-|          eu |      france |       Total |        2.5 | 0.21739130434782608 |
-|          eu |      france |        lyon |        0.5 |                 0.2 |
-|          eu |      france |       paris |        2.0 |                 0.8 |
-|          eu |          uk |       Total |        9.0 |   0.782608695652174 |
-|          eu |          uk |      london |        9.0 |                 1.0 |
-+-------------+-------------+-------------+------------+---------------------+
++-------------+-------------+-------------+------------+---------------------+---------------------+
+|   continent |     country |        city | population |        parent ratio |            gt ratio |
++-------------+-------------+-------------+------------+---------------------+---------------------+
+| Grand Total | Grand Total | Grand Total |       35.0 |                 1.0 |                 1.0 |
+|          am |       Total |       Total |       24.0 |  0.6857142857142857 |  0.6857142857142857 |
+|          am |      canada |       Total |       12.0 |                 0.5 | 0.34285714285714286 |
+|          am |      canada |    montreal |        7.0 |  0.5833333333333334 |                 0.2 |
+|          am |      canada |       otawa |        2.0 | 0.16666666666666666 | 0.05714285714285714 |
+|          am |      canada |     toronto |        3.0 |                0.25 | 0.08571428571428572 |
+|          am |         usa |       Total |       12.0 |                 0.5 | 0.34285714285714286 |
+|          am |         usa |     chicago |        4.0 |  0.3333333333333333 | 0.11428571428571428 |
+|          am |         usa |         nyc |        8.0 |  0.6666666666666666 | 0.22857142857142856 |
+|          eu |       Total |       Total |       11.0 |  0.3142857142857143 |  0.3142857142857143 |
+|          eu |      france |       Total |        3.0 |  0.2727272727272727 | 0.08571428571428572 |
+|          eu |      france |        lyon |        1.0 |  0.3333333333333333 | 0.02857142857142857 |
+|          eu |      france |       paris |        2.0 |  0.6666666666666666 | 0.05714285714285714 |
+|          eu |          uk |       Total |        8.0 |  0.7272727272727273 | 0.22857142857142856 |
+|          eu |          uk |      london |        8.0 |                 1.0 | 0.22857142857142856 |
++-------------+-------------+-------------+------------+---------------------+---------------------+
 ```
 
 ##### Group comparison - ColumnSet
diff --git a/js/typescript-library/package-lock.json b/js/typescript-library/package-lock.json
index c279b46..d874124 100644
--- a/js/typescript-library/package-lock.json
+++ b/js/typescript-library/package-lock.json
@@ -1,12 +1,12 @@
 {
   "name": "@squashql/squashql-js",
-  "version": "1.9.0",
+  "version": "1.10.0",
   "lockfileVersion": 2,
   "requires": true,
   "packages": {
     "": {
       "name": "@squashql/squashql-js",
-      "version": "1.9.0",
+      "version": "1.10.0",
       "license": "Apache-2.0",
       "dependencies": {
         "axios": "^1.6.7"
diff --git a/js/typescript-library/src/generate-json-queryDto.ts b/js/typescript-library/src/generate-json-queryDto.ts
index 5b1b8d9..31d03e4 100644
--- a/js/typescript-library/src/generate-json-queryDto.ts
+++ b/js/typescript-library/src/generate-json-queryDto.ts
@@ -2,7 +2,7 @@ import {JoinType, Query, Table} from "./query"
 import {
   AggregatedMeasure,
   avgIf,
-  BinaryOperationMeasure, BinaryOperator,
+  BinaryOperationMeasure, BinaryOperator, comparisonMeasureWithGrandTotalAlongAncestors,
   comparisonMeasureWithinSameGroup,
   comparisonMeasureWithParent,
   comparisonMeasureWithPeriod,
@@ -74,6 +74,7 @@ export function generateFromQueryDto() {
     [tableField("Month"), "m"]
   ]), new Month(tableField("Month"), tableField("Year"))))
   q.withMeasure(comparisonMeasureWithParent("parent", ComparisonMethod.DIVIDE, price, [tableField("Year"), tableField("Month")]))
+  q.withMeasure(comparisonMeasureWithGrandTotalAlongAncestors("grandTotalAlongAncestors", ComparisonMethod.DIVIDE, price, [tableField("Year"), tableField("Month")]))
 
   const queryCondition = or(or(and(eq("a"), eq("b")), lt(5)), like("a%"))
   q.withWhereCriteria(all([
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index b6cffda..101ed2d 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -15,7 +15,7 @@ export {
   sumIf, minIf, maxIf, avgIf, countIf, countDistinctIf,
   plus, minus, multiply, divide,
   integer, decimal,
-  comparisonMeasureWithPeriod, comparisonMeasureWithinSameGroup, comparisonMeasureWithParent,
+  comparisonMeasureWithPeriod, comparisonMeasureWithinSameGroup, comparisonMeasureWithParent, comparisonMeasureWithGrandTotalAlongAncestors,
   totalCount,
   ComparisonMethod,
 } from './measure'
diff --git a/js/typescript-library/src/measure.ts b/js/typescript-library/src/measure.ts
index a47b964..4f76631 100644
--- a/js/typescript-library/src/measure.ts
+++ b/js/typescript-library/src/measure.ts
@@ -11,6 +11,7 @@ export interface Measure {
 }
 
 export type BasicMeasure = Measure
+
 export class AggregatedMeasure implements BasicMeasure {
   readonly class: string = PACKAGE + "AggregatedMeasure"
   readonly field: Field
@@ -104,7 +105,8 @@ export class ComparisonMeasureReferencePosition implements Measure {
               private referencePosition: Map<Field, string>,
               private columnSetKey?: ColumnSetKey,
               private period?: Period,
-              private ancestors?: Array<Field>) {
+              private ancestors?: Array<Field>,
+              private grandTotalAlongAncestors?: boolean) {
     this.alias = alias
   }
 
@@ -118,6 +120,7 @@ export class ComparisonMeasureReferencePosition implements Measure {
       "period": this.period,
       "referencePosition": this.referencePosition ? Object.fromEntries(serializeMap(this.referencePosition)) : undefined,
       "ancestors": this.ancestors,
+      "grandTotalAlongAncestors": this.grandTotalAlongAncestors,
     }
   }
 }
@@ -262,5 +265,12 @@ export function comparisonMeasureWithParent(alias: string,
                                             comparisonMethod: ComparisonMethod,
                                             measure: Measure,
                                             ancestors: Array<Field>): Measure {
-  return new ComparisonMeasureReferencePosition(alias, comparisonMethod, measure, undefined, undefined, undefined, ancestors)
+  return new ComparisonMeasureReferencePosition(alias, comparisonMethod, measure, undefined, undefined, undefined, ancestors, false)
+}
+
+export function comparisonMeasureWithGrandTotalAlongAncestors(alias: string,
+                                                              comparisonMethod: ComparisonMethod,
+                                                              measure: Measure,
+                                                              ancestors: Array<Field>): Measure {
+  return new ComparisonMeasureReferencePosition(alias, comparisonMethod, measure, undefined, undefined, undefined, ancestors, true)
 }


Test patch:
diff --git a/bigquery/src/test/java/io/squashql/query/TestBigQueryGrandTotalAlongAncestorsComparison.java b/bigquery/src/test/java/io/squashql/query/TestBigQueryGrandTotalAlongAncestorsComparison.java
new file mode 100644
index 0000000..3b16e86
--- /dev/null
+++ b/bigquery/src/test/java/io/squashql/query/TestBigQueryGrandTotalAlongAncestorsComparison.java
@@ -0,0 +1,51 @@
+package io.squashql.query;
+
+import io.squashql.BigQueryDatastore;
+import io.squashql.BigQueryServiceAccountDatastore;
+import io.squashql.query.database.BigQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.BigQueryDataLoader;
+import io.squashql.transaction.DataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+import static io.squashql.query.BigQueryTestUtil.DATASET_NAME;
+import static io.squashql.query.BigQueryTestUtil.PROJECT_ID;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.BigQueryClassTemplateGenerator}.
+ */
+public class TestBigQueryGrandTotalAlongAncestorsComparison extends ATestGrandTotalAlongAncestorsComparison {
+
+  @AfterAll
+  void tearDown() {
+    this.fieldsByStore.forEach((store, fields) -> BigQueryTestUtil.deleteTable(((BigQueryDatastore) this.datastore).getBigquery(), store));
+  }
+
+  @Override
+  protected void createTables() {
+    BigQueryDataLoader tm = (BigQueryDataLoader) this.tm;
+    BigQueryTestUtil.createDatasetIfDoesNotExist(tm.getBigQuery(), DATASET_NAME);
+    this.fieldsByStore.forEach((store, fields) -> tm.dropAndCreateInMemoryTable(store, fields));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new BigQueryEngine((BigQueryDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new BigQueryServiceAccountDatastore(BigQueryTestUtil.createServiceAccountCredentials(), PROJECT_ID, DATASET_NAME);
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new BigQueryDataLoader(((BigQueryDatastore) this.datastore).getBigquery(), DATASET_NAME);
+  }
+
+  @Override
+  protected Object translate(Object o) {
+    return BigQueryTestUtil.translate(o);
+  }
+}
diff --git a/clickhouse/src/test/java/io/squashql/query/TestClickHouseGrandTotalAlongAncestorsComparison.java b/clickhouse/src/test/java/io/squashql/query/TestClickHouseGrandTotalAlongAncestorsComparison.java
new file mode 100644
index 0000000..88d6729
--- /dev/null
+++ b/clickhouse/src/test/java/io/squashql/query/TestClickHouseGrandTotalAlongAncestorsComparison.java
@@ -0,0 +1,53 @@
+package io.squashql.query;
+
+import io.squashql.ClickHouseDatastore;
+import io.squashql.query.database.ClickHouseQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.template.ClickHouseClassTemplateGenerator;
+import io.squashql.transaction.ClickHouseDataLoader;
+import io.squashql.transaction.DataLoader;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.BeforeAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link ClickHouseClassTemplateGenerator}.
+ */
+public class TestClickHouseGrandTotalAlongAncestorsComparison extends ATestGrandTotalAlongAncestorsComparison {
+
+  public org.testcontainers.containers.GenericContainer container = ClickHouseTestUtil.createClickHouseContainer();
+
+  @BeforeAll
+  @Override
+  void setup() {
+    this.container.start();
+    super.setup();
+  }
+
+  @AfterAll
+  void tearDown() {
+    // we do not stop the container to be able to reuse it between tests.
+    ((ClickHouseDataLoader) this.tm).dropTables(this.fieldsByStore.keySet());
+  }
+
+  @Override
+  protected void createTables() {
+    ClickHouseDataLoader tm = (ClickHouseDataLoader) this.tm;
+    this.fieldsByStore.forEach((store, fields) -> tm.dropAndCreateInMemoryTable(store, fields));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new ClickHouseQueryEngine((ClickHouseDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new ClickHouseDatastore(ClickHouseTestUtil.jdbcUrl.apply(this.container));
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new ClickHouseDataLoader(((ClickHouseDatastore) this.datastore).dataSource);
+  }
+}
diff --git a/core/src/test/java/io/squashql/query/ATestGrandTotalAlongAncestorsComparison.java b/core/src/test/java/io/squashql/query/ATestGrandTotalAlongAncestorsComparison.java
new file mode 100644
index 0000000..dfc5a5b
--- /dev/null
+++ b/core/src/test/java/io/squashql/query/ATestGrandTotalAlongAncestorsComparison.java
@@ -0,0 +1,142 @@
+package io.squashql.query;
+
+import io.squashql.TestClass;
+import io.squashql.query.builder.Query;
+import io.squashql.query.dto.QueryDto;
+import io.squashql.table.Table;
+import io.squashql.type.TableTypedField;
+import org.assertj.core.api.Assertions;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.TestInstance;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.Map;
+
+import static io.squashql.query.TableField.tableField;
+import static io.squashql.query.TableField.tableFields;
+import static io.squashql.query.database.QueryEngine.GRAND_TOTAL;
+import static io.squashql.query.database.QueryEngine.TOTAL;
+import static io.squashql.transaction.DataLoader.MAIN_SCENARIO_NAME;
+
+@TestClass
+@TestInstance(TestInstance.Lifecycle.PER_CLASS)
+public abstract class ATestGrandTotalAlongAncestorsComparison extends ABaseTestQuery {
+
+  private final String storeName = "store" + getClass().getSimpleName().toLowerCase();
+  private final List<Field> fields = tableFields(List.of("continent", "country", "city")); // ancestors
+  private final Measure amount = Functions.sum("amount", "amount");
+  private final ComparisonMeasureReferencePosition percentOfGT = new ComparisonMeasureReferencePosition("percentOfParent", ComparisonMethod.DIVIDE, this.amount, this.fields, true);
+
+  @Override
+  protected Map<String, List<TableTypedField>> getFieldsByStore() {
+    TableTypedField city = new TableTypedField(this.storeName, "city", String.class);
+    TableTypedField country = new TableTypedField(this.storeName, "country", String.class);
+    TableTypedField continent = new TableTypedField(this.storeName, "continent", String.class);
+    TableTypedField spendingCategory = new TableTypedField(this.storeName, "spending_category", String.class);
+    TableTypedField amount = new TableTypedField(this.storeName, "amount", double.class);
+    return Map.of(this.storeName, List.of(city, country, continent, spendingCategory, amount));
+  }
+
+  @Override
+  protected void loadData() {
+    this.tm.load(MAIN_SCENARIO_NAME, this.storeName, List.of(
+            new Object[]{"paris", "france", "eu", "car", 1d},
+            new Object[]{"paris", "france", "eu", "home", 2d},
+            new Object[]{"paris", "france", "eu", "hobbies", 1d},
+            new Object[]{"lyon", "france", "eu", "car", 0.1d},
+            new Object[]{"lyon", "france", "eu", "home", 2d},
+            new Object[]{"lyon", "france", "eu", "hobbies", 1d},
+            new Object[]{"london", "uk", "eu", "car", 2d},
+            new Object[]{"london", "uk", "eu", "home", 2d},
+            new Object[]{"london", "uk", "eu", "hobbies", 5d}
+    ));
+  }
+
+  @Test
+  void testSimple() {
+    QueryDto query = Query
+            .from(this.storeName)
+            .select(this.fields, List.of(this.amount, this.percentOfGT))
+            .build();
+
+    Table result = this.executor.executeQuery(query);
+    double gt = 16.1d; // amount on GrandTotal
+    Assertions.assertThat(result).containsExactly(
+            Arrays.asList("eu", "france", "lyon", 3.1d, 3.1d / gt),
+            Arrays.asList("eu", "france", "paris", 4d, 4d / gt),
+            Arrays.asList("eu", "uk", "london", 9d, 9d / gt));
+
+    query = Query
+            .from(this.storeName)
+            .select(this.fields, List.of(this.amount, this.percentOfGT))
+            .rollup(this.fields)
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result).containsExactly(
+            Arrays.asList(GRAND_TOTAL, GRAND_TOTAL, GRAND_TOTAL, 16.1d, 1d),
+            Arrays.asList("eu", TOTAL, TOTAL, 16.1d, 1d),
+            Arrays.asList("eu", "france", TOTAL, 7.1d, 7.1d / gt),
+            Arrays.asList("eu", "france", "lyon", 3.1d, 3.1d / gt),
+            Arrays.asList("eu", "france", "paris", 4d, 4d / gt),
+            Arrays.asList("eu", "uk", TOTAL, 9d, 9d / gt),
+            Arrays.asList("eu", "uk", "london", 9d, 9d / gt));
+  }
+
+  @Test
+  void testCrossjoinWithOtherColumn() {
+    QueryDto query = Query
+            .from(this.storeName)
+            .select(tableFields(List.of("spending_category", "continent", "country", "city")), List.of(this.amount, this.percentOfGT))
+            .build();
+
+    Table result = this.executor.executeQuery(query);
+    double gtCar = 3.1d;
+    double gtHome = 6d;
+    double gtHobbies = 7d;
+    Assertions.assertThat(result).containsExactly(
+            Arrays.asList("car", "eu", "france", "lyon", 0.1d, 0.1d / gtCar),
+            Arrays.asList("car", "eu", "france", "paris", 1d, 1d / gtCar),
+            Arrays.asList("car", "eu", "uk", "london", 2d, 2d / gtCar),
+            Arrays.asList("hobbies", "eu", "france", "lyon", 1d, 1d / gtHobbies),
+            Arrays.asList("hobbies", "eu", "france", "paris", 1d, 1d / gtHobbies),
+            Arrays.asList("hobbies", "eu", "uk", "london", 5d, 5d / gtHobbies),
+            Arrays.asList("home", "eu", "france", "lyon", 2d, 2d / gtHome),
+            Arrays.asList("home", "eu", "france", "paris", 2d, 2d / gtHome),
+            Arrays.asList("home", "eu", "uk", "london", 2d, 2d / gtHome));
+  }
+
+  @Test
+  void testCrossjoinWithOtherColumnAnFilters() {
+    // Filter on other category
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(Functions.criterion(tableField("spending_category"), Functions.eq("car")))
+            .select(tableFields(List.of("spending_category", "continent", "country", "city")), List.of(this.amount, this.percentOfGT))
+            .build();
+
+    Table result = this.executor.executeQuery(query);
+    double gtCar = 3.1d;
+    Assertions.assertThat(result).containsExactly(
+            Arrays.asList("car", "eu", "france", "lyon", 0.1d, 0.1d / gtCar),
+            Arrays.asList("car", "eu", "france", "paris", 1d, 1d / gtCar),
+            Arrays.asList("car", "eu", "uk", "london", 2d, 2d / gtCar));
+
+    query = Query
+            .from(this.storeName)
+            .where(Functions.criterion(tableField("country"), Functions.eq("france")))
+            .select(tableFields(List.of("spending_category", "continent", "country", "city")), List.of(this.amount, this.percentOfGT))
+            .build();
+
+    result = this.executor.executeQuery(query);
+    double gtHome = 6d;
+    double gtHobbies = 7d;
+    Assertions.assertThat(result).containsExactly(
+            Arrays.asList("car", "eu", "france", "lyon", 0.1d, 0.1d / gtCar),
+            Arrays.asList("car", "eu", "france", "paris", 1d, 1d / gtCar),
+            Arrays.asList("hobbies", "eu", "france", "lyon", 1d, 1d / gtHobbies),
+            Arrays.asList("hobbies", "eu", "france", "paris", 1d, 1d / gtHobbies),
+            Arrays.asList("home", "eu", "france", "lyon", 2d, 2d / gtHome),
+            Arrays.asList("home", "eu", "france", "paris", 2d, 2d / gtHome));
+  }
+}
diff --git a/core/src/test/java/io/squashql/query/ATestPeriodComparison.java b/core/src/test/java/io/squashql/query/ATestPeriodComparison.java
index f48870e..04595f0 100644
--- a/core/src/test/java/io/squashql/query/ATestPeriodComparison.java
+++ b/core/src/test/java/io/squashql/query/ATestPeriodComparison.java
@@ -442,7 +442,7 @@ public abstract class ATestPeriodComparison extends ABaseTestQuery {
     Assertions.assertThat(finalTable).containsExactlyInAnyOrder(
             List.of(yearType(2022), 12L),
             List.of(yearType(2023), 12L),
-            List.of("Grand Total", 24L));
+            List.of(GRAND_TOTAL, 24L));
   }
 
   protected Object yearType(int i) {
diff --git a/duckdb/src/test/java/io/squashql/query/TestDuckDBGrandTotalAlongAncestorsComparison.java b/duckdb/src/test/java/io/squashql/query/TestDuckDBGrandTotalAlongAncestorsComparison.java
new file mode 100644
index 0000000..8d4657e
--- /dev/null
+++ b/duckdb/src/test/java/io/squashql/query/TestDuckDBGrandTotalAlongAncestorsComparison.java
@@ -0,0 +1,35 @@
+package io.squashql.query;
+
+import io.squashql.DuckDBDatastore;
+import io.squashql.query.database.DuckDBQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.DuckDBDataLoader;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.DuckDBClassTemplateGenerator}.
+ */
+public class TestDuckDBGrandTotalAlongAncestorsComparison extends ATestGrandTotalAlongAncestorsComparison {
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new DuckDBQueryEngine((DuckDBDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new DuckDBDatastore();
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new DuckDBDataLoader((DuckDBDatastore) this.datastore);
+  }
+
+  @Override
+  protected void createTables() {
+    DuckDBDataLoader tm = (DuckDBDataLoader) this.tm;
+    this.fieldsByStore.forEach(tm::createOrReplaceTable);
+  }
+}
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index f4658e4..a227f19 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -73,6 +73,11 @@ public class TestJavascriptLibrary {
             ComparisonMethod.DIVIDE,
             price,
             List.of(tableField("Year"), tableField("Month"))));
+    q.withMeasure(new ComparisonMeasureReferencePosition("grandTotalAlongAncestors",
+            ComparisonMethod.DIVIDE,
+            price,
+            List.of(tableField("Year"), tableField("Month")),
+            true));
 
     var queryCondition = or(and(eq("a"), eq("b")), lt(5), like("a%"));
     q.withCondition(tableField("f1"), queryCondition);
diff --git a/snowflake/src/test/java/io/squashql/query/TestSnowflakeGrandTotalAlongAncestorsComparison.java b/snowflake/src/test/java/io/squashql/query/TestSnowflakeGrandTotalAlongAncestorsComparison.java
new file mode 100644
index 0000000..32143ff
--- /dev/null
+++ b/snowflake/src/test/java/io/squashql/query/TestSnowflakeGrandTotalAlongAncestorsComparison.java
@@ -0,0 +1,52 @@
+package io.squashql.query;
+
+import io.squashql.SnowflakeDatastore;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.query.database.SnowflakeQueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.SnowflakeDataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.SnowflakeClassTemplateGenerator}.
+ */
+public class TestSnowflakeGrandTotalAlongAncestorsComparison extends ATestGrandTotalAlongAncestorsComparison {
+
+  @AfterAll
+  void tearDown() {
+    SnowflakeDataLoader tm = (SnowflakeDataLoader) this.tm;
+    this.fieldsByStore.forEach((storeName, storeFields) -> tm.dropTable(storeName));
+  }
+
+  @Override
+  protected void createTables() {
+    SnowflakeDataLoader tm = (SnowflakeDataLoader) this.tm;
+    this.fieldsByStore.forEach(tm::createOrReplaceTable);
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new SnowflakeQueryEngine((SnowflakeDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new SnowflakeDatastore(
+            SnowflakeTestUtil.jdbcUrl,
+            SnowflakeTestUtil.database,
+            SnowflakeTestUtil.schema,
+            SnowflakeTestUtil.properties
+    );
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new SnowflakeDataLoader((SnowflakeDatastore) this.datastore);
+  }
+
+  @Override
+  protected Object translate(Object o) {
+    return SnowflakeTestUtil.translate(o);
+  }
+}
diff --git a/spark/src/test/java/io/squashql/query/TestSparkGrandTotalAlongAncestorsComparison.java b/spark/src/test/java/io/squashql/query/TestSparkGrandTotalAlongAncestorsComparison.java
new file mode 100644
index 0000000..4b608f3
--- /dev/null
+++ b/spark/src/test/java/io/squashql/query/TestSparkGrandTotalAlongAncestorsComparison.java
@@ -0,0 +1,42 @@
+package io.squashql.query;
+
+import io.squashql.SparkDatastore;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.query.database.SparkQueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.SparkDataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.SparkClassTemplateGenerator}.
+ */
+public class TestSparkGrandTotalAlongAncestorsComparison extends ATestGrandTotalAlongAncestorsComparison {
+
+  @AfterAll
+  void tearDown() {
+    this.fieldsByStore.keySet().forEach(storeName -> ((SparkDatastore) this.datastore).spark.catalog().dropTempView(storeName));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new SparkQueryEngine((SparkDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new SparkDatastore();
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    SparkDatastore ds = (SparkDatastore) this.datastore;
+    return new SparkDataLoader(ds.spark);
+  }
+
+  @Override
+  protected void createTables() {
+    SparkDataLoader tm = (SparkDataLoader) this.tm;
+    this.fieldsByStore.forEach((store, fields) -> tm.createTemporaryTable(store, fields));
+  }
+}