axmo: "1.0"
name: Currency conversion
description: >-
  Recipe — a rate that belongs to a higher level than the data. Each entity reports sales in its own currency.
  The entity tree groups entities under their currency, so the tree itself is the mapping from entity to
  currency. Exchange rates are loaded once per currency and copied down to its entities. Sales in local currency
  are not added across entities (different currencies); sales in euro are.
dimensions:
  Entities:
    type: categorical
    layers: [Top_Entities, Currency, Entity]
    tree:
      All_Entities:
        EUR:
          - Italia Srl
          - Deutschland GmbH
        GBP:
          - UK Ltd
        USD:
          - US Inc
  Time:
    type: sequential
    sequentialType: axmo_time
    parameters:
      selected_levels: [month, quarter]
      start: "2027-01"
      end_mode: end_point
      end: "2027-03"
series:
  Sales LC:
    type: input
    description: Sales in the entity's own currency. Not added across entities, because currencies differ.
    dimensions:
      - Entities:
          rollUp: NONE
      - Time:
          rollUp: SUM
  FX Rate:
    type: input
    description: Euro per unit of the currency, monthly average. Loaded per currency and copied to its entities.
    dimensions:
      - Entities:
          rollUp: NONE
          splitDown: COPY
      - Time:
          rollUp: AVG
  Sales EUR:
    type: calculated
    description: Sales converted to euro at each month's rate, then added up.
    formula: Sales LC * FX Rate
    dimensions: [Entities, Time]
dataSources:
  Local sales:
    sourceType: excel-range
    rangeRef: Inputs!A1:C13
    hasHeaders: true
    shape: table
  Rates:
    sourceType: excel-range
    rangeRef: Inputs!E1:G10
    hasHeaders: true
    shape: table
dataLoaders:
  Load sales:
    type: seriesLoader
    source: Local sales
    target: Sales LC
    loadMode: table
    valueColumn: Sales
    duplicateHandling: BLOCK
    dimensionMappings:
      Entities: { column: Entity }
      Time: { column: Month }
  Load rates:
    type: seriesLoader
    source: Rates
    target: FX Rate
    loadMode: table
    valueColumn: EUR per unit
    duplicateHandling: BLOCK
    dimensionMappings:
      Entities: { column: Currency }
      Time: { column: Month }
outputRanges:
  Sales in euro:
    cell: Report!A1
    rowDef:
      blocks:
        - dimension: Entities/Entity
        - dimension: Entities/Currency
        - dimension: Entities/Top_Entities
    colDef:
      blocks:
        - dimension: Time/Month
        - dimension: Time/Quarter
        - series: Sales EUR
          format: "#,##0.00"
          newLine: true
data:
  Inputs!A1:C13:
    values:
      - [Entity, Month, Sales]
      - [Italia Srl, "2027-01", 120000]
      - [Italia Srl, "2027-02", 110000]
      - [Italia Srl, "2027-03", 130000]
      - [Deutschland GmbH, "2027-01", 200000]
      - [Deutschland GmbH, "2027-02", 190000]
      - [Deutschland GmbH, "2027-03", 210000]
      - [UK Ltd, "2027-01", 80000]
      - [UK Ltd, "2027-02", 85000]
      - [UK Ltd, "2027-03", 90000]
      - [US Inc, "2027-01", 150000]
      - [US Inc, "2027-02", 160000]
      - [US Inc, "2027-03", 155000]
  Inputs!E1:G10:
    values:
      - [Currency, Month, EUR per unit]
      - [EUR, "2027-01", 1]
      - [EUR, "2027-02", 1]
      - [EUR, "2027-03", 1]
      - [GBP, "2027-01", 1.18]
      - [GBP, "2027-02", 1.16]
      - [GBP, "2027-03", 1.19]
      - [USD, "2027-01", 0.92]
      - [USD, "2027-02", 0.93]
      - [USD, "2027-03", 0.95]