axmo: "1.0"
name: Formula forms
description: >-
  Recipe: the formula forms most models need beyond arithmetic. Comparisons and IF, and/or/not, powers, ROUND,
  two-argument MAX, ISEMPTY, an element range, the horizon's first month, a running total from it, a
  year-to-date total that restarts each year, and a layer read as a set. Each
  calculated series totals with NONE, so only the months are meaningful.
dimensions:
  Products:
    type: categorical
    layers: [Top_Products, Product]
    tree:
      All_Products: [A, B]
  Time:
    type: sequential
    sequentialType: axmo_time
    parameters: {selected_levels: [month, quarter, year], start: "2027-01", end_mode: end_point, end: "2028-02"}
series:
  Sales: {type: input, description: Sales in EUR., dimensions: [Products, Time]}
  Units: {type: input, description: Units sold., dimensions: [Products, Time]}
  Big: {type: calculated, formula: 'IF(Sales > 100, 1, 0)', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Both: {type: calculated, formula: 'IF(Sales > 100 and Units >= 5, 1, 0)', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Units Squared: {type: calculated, formula: 'Units ^ 2', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Minus Squared: {type: calculated, formula: '-2 ^ 2', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Power Chain: {type: calculated, formula: '2 ^ 3 ^ 2', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Rounded: {type: calculated, formula: 'ROUND(Sales / 7, 2)', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Larger: {type: calculated, formula: 'MAX(Sales, Units * 30)', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  First Month: {type: calculated, formula: 'Sales[Month[$1]]', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Jan to Feb: {type: calculated, formula: 'SUM(Sales[2027-01:2027-02])', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Running Total: {type: calculated, formula: 'SUM(Sales[Month[$1:0]])', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Year To Date: {type: calculated, formula: 'SUM(Sales[Year[0]/Month[$1:0]])', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Layer Total: {type: calculated, formula: 'SUM(Sales[Product])', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Units Empty: {type: calculated, formula: 'IF(ISEMPTY(Units), 1, 0)', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Not 120: {type: calculated, formula: 'IF(Sales <> 120, 1, 0)', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
  Not Big: {type: calculated, formula: 'IF(not (Sales > 100), 1, 0)', dimensions: [{Products: {rollUp: NONE}}, {Time: {rollUp: NONE}}]}
dataSources:
  In: {sourceType: excel-range, rangeRef: "In!A1:D7", hasHeaders: true, shape: table}
dataLoaders:
  Load:
    type: seriesLoader
    source: In
    loadMode: table
    targets: [{target: Sales, valueColumn: Sales}, {target: Units, valueColumn: Units}]
    dimensionMappings: {Products: {column: Product}, Time: {column: Month}}
data:
  In!A1:D7:
    values:
      - [Product, Month, Sales, Units]
      - [A, "2027-01", 120, 4]
      - [A, "2027-02", 90, 6]
      - [A, "2027-03", 150, 5]
      - [B, "2027-01", 60, 2]
      - [B, "2027-02", 200, 8]
      - [A, "2028-01", 50, 1]