Mockup Table

Query

SELECT 'A' AS class, 'x' AS type, DATE('2023-10-17') AS `date`, 37 AS quantity, 2053.5 AS value UNION ALL SELECT 'B' AS class, 'y' AS type, DATE('2023-10-25') AS `date`, 23 AS quantity, 943.0 AS value UNION ALL SELECT 'A' AS class, 'z' AS type, DATETIME('2023-12-20') AS `date`, 0 AS quantity, 0 AS value UNION ALL SELECT 'B' AS class, 'x' AS type, DATE('2023-12-30') AS `date`, 45 AS quantity, 1696.5 AS value UNION ALL SELECT 'A' AS class, 'y' AS type, DATE('2024-01-07') AS `date`, 52 AS quantity, 1138.8 AS value UNION ALL SELECT 'B' AS class, 'z' AS type, DATE('2024-02-22') AS `date`, 9 AS quantity, 514.8 AS value;

Table

Calculate Field Formulas

Basic Operations

Uminus

summarized by SUM

• =UMINUS(value)

• =(-1) * value

Divide

summarized by SUM

• =DIVIDE(value,quantity)

• =value / quantity

Multiply

summarized by SUM

• =MULTIPLY(quantity,2)

• =quantity * 2

Add

summarized by SUM

• =ADD(quantity,50)

• =quantity + 50

⚠️ The total is as well only +50, not +(n * 50)

Minus

summarized by SUM

• =MINUS(quantity,25)

• =quantity - 25

⚠️ The total is as well only -25, not -(n * 25)

Conditions

Countif

summarized by CUSTOM

• =COUNTIF(class,"A")

Sumif

summarized by CUSTOM

• =SUMIF(class,"A",quantity)

• =SUMIF(type,"x",value)

Comparision

Greater than

summarized by SUM

• =GT(quantity,37)

• =quantity > 37

Greater than or equal

summarized by SUM

• =GTE(quantity,37)

• =quantity >= 37

Less than

summarized by SUM

• =LT(quantity,37)

• =quantity < 37

Less than or equal

summarized by SUM

• =LTE(quantity,37)

• =quantity <= 37