Dollar conversion

Objective:

Use the PTAX exchange rate to convert billing amounts in USD to another currency, as needed.

There are two factors in this process:

• Apply a specific PTAX rate for Marketplace items.

• Apply a different PTAX rate for other billing items.

---

Use Case:

Create a visualization that shows the billing cost converted to BRL (R$) or another currency.

SQL:

CASE
  WHEN "bill/billingentity" = 'Marketplace' THEN
    CASE
      WHEN "bill/billingperiodstartdate" = CAST('2024-06-01' AS DATE) THEN 6.5541155
      WHEN "bill/billingperiodstartdate" = CAST('2024-07-01' AS DATE) THEN 7.5547712
      WHEN "bill/billingperiodstartdate" = CAST('2024-08-01' AS DATE) THEN 8.6327960
      WHEN "bill/billingperiodstartdate" = CAST('2024-09-01' AS DATE) THEN 7.7832092
      WHEN "bill/billingperiodstartdate" = CAST('2024-10-01' AS DATE) THEN 7.990898
      WHEN "bill/billingperiodstartdate" = CAST('2024-11-01' AS DATE) THEN 8.99089
      WHEN "bill/billingperiodstartdate" = CAST('2024-12-01' AS DATE) THEN 7.99089
      WHEN "bill/billingperiodstartdate" = CAST('2025-01-01' AS DATE) THEN 6.813503
      WHEN "bill/billingperiodstartdate" = CAST('2025-02-01' AS DATE) THEN 5.2655620
      WHEN "bill/billingperiodstartdate" = CAST('2025-03-01' AS DATE) THEN 7.8920
      WHEN "bill/billingperiodstartdate" = CAST('2025-04-01' AS DATE) THEN 7.107108
      WHEN "bill/billingperiodstartdate" = CAST('2025-05-01' AS DATE) THEN 9.272041
      ELSE 5.83768409038
    END
  ELSE
    CASE
      WHEN "bill/billingperiodstartdate" = CAST('2024-06-01' AS DATE) THEN 5.451265425
      WHEN "bill/billingperiodstartdate" = CAST('2024-07-01' AS DATE) THEN 5.59990108722
      WHEN "bill/billingperiodstartdate" = CAST('2024-08-01' AS DATE) THEN 5.60968690955
      WHEN "bill/billingperiodstartdate" = CAST('2024-09-01' AS DATE) THEN 5.59648214329
      WHEN "bill/billingperiodstartdate" = CAST('2024-10-01' AS DATE) THEN 5.680644
      WHEN "bill/billingperiodstartdate" = CAST('2024-11-01' AS DATE) THEN 5.380644
      WHEN "bill/billingperiodstartdate" = CAST('2024-12-01' AS DATE) THEN 5.280644
      WHEN "bill/billingperiodstartdate" = CAST('2025-01-01' AS DATE) THEN 5.00579
      WHEN "bill/billingperiodstartdate" = CAST('2025-02-01' AS DATE) THEN 5.056643
      WHEN "bill/billingperiodstartdate" = CAST('2025-03-01' AS DATE) THEN 5.03128133367
      WHEN "bill/billingperiodstartdate" = CAST('2025-04-01' AS DATE) THEN 5.17799225
      WHEN "bill/billingperiodstartdate" = CAST('2025-05-01' AS DATE) THEN 5.1929545213
      ELSE 5.680644
    END
END * "lineitem/netunblendedcost"

---