Specific cases & Features - MaksimAbelchuk/RnS_Docs GitHub Wiki

Auto updatable date of the last transaction using a timestamp

You can add a date that will be auto-updated depending on the dimensions shown.

Example:

Showing different dates for the same dataset.

Client	Last Date
A	20.10.2020

How to add

1. Add a calculated measure to your dataset config.
2. Delete all scales from config.

calculated_measures:
- name: time_date
  title: time_date
  category: time
  formula: MAX("time") # formula: MAX("time")::date OR to_char(MAX("time"), 'Day, DD  HH12:MI:SS')
  transaction_formula: MAX("time") # transaction_formula: MAX("time")::date OR to_char(MAX("time"), 'Day, DD  HH12:MI:SS')

Add calculated measures into other calculated measures

You can add a calculated measure inside of another if you need to create a really complex measure. Imagine you have these measures:

calculated_measures:
- name: EBITDA
  title: EBITDA
  scales_id: numbers
  units_id: no_units
  formula: SUM("operating_income")
  transaction_formula: '"operating_income"'
- name: SGA
  title: Total SGA
  scales_id: numbers
  units_id: no_units
  formula: SUM("other_costs")
  transaction_formula: '"other_costs"'
- name: COGS
  title: Total COGS
  scales_id: numbers
  units_id: no_units
  formula: SUM("variable_costs" + "fixed_manufacturing_costs")
  transaction_formula: '"variable_costs" + "fixed_manufacturing_costs"'
- name: reductions
  title: Total Reductions
  scales_id: numbers
  units_id: no_units
  formula: SUM("on_invoice_costs" + "logistics_payment_terms")
  transaction_formula: '"on_invoice_costs" + "logistics_payment_terms"'

And you need add another measure calculated by this formula: Total Revenue = SUM ( EBITDA + SGA + COGS + Reductions). Then, you can use them in creating an SQL formula for the new calculated measures:

- name: total_revenue
  title: Total Revenue
  scales_id: numbers
  units_id: no_units
  formula: SUM("operating_income" + "other_costs" + "variable_costs" + "fixed_manufacturing_costs" + "on_invoice_costs" + "logistics_payment_terms")
  transaction_formula: '"operating_income" + "other_costs" + "variable_costs" + "fixed_manufacturing_costs" + "on_invoice_costs" + "logistics_payment_terms"'
- name: EBITDA_Margin
  title: EBITDA Margin %
  scales_id: numbers
  units_id: no_units
  formula: SUM("operating_income") / NULLIF(SUM("operating_income" + "other_costs" + "variable_costs" + "fixed_manufacturing_costs" + "on_invoice_costs" + "logistics_payment_terms"), 0)
  transaction_formula: '"operating_income" / NULLIF("operating_income" + "other_costs" + "variable_costs" + "fixed_manufacturing_costs" + "on_invoice_costs" + "logistics_payment_terms", 0)'

Search by special symbols

Due to security issues ability to add special symbols into rows values or column names has been restricted.

Allowed to use symbols are:

• Numbers: 0-9
• Letters: a-z, A-Z
• Brackets: ( )
• Underscore: _
• Hyphen: -
• Ampersand: &
• Slash: \

To get the symbols back to rows

1. Add the needed symbol in ASCII form to the value of a row.
2. Set 'decode_html_db_value' to 'true' via dataset config to all needed fields (dimensions or measures).

dimensions:
  - name: country
    db_field: Country
    title: Country
    decode_html_db_value: true

3. Use the rows with the symbols decoded.

Note, that there is no possibility to show these symbols in column names at the moment.

Decimal places

You can configure decimal places for the whole dataset or for a specific measure.

Example with setting decimal places for specific measure:

db_measures:
- name: promoted_price
  db_field: PROMOTED_PRICE
  title: Promoted Price
  type: float
  category: promotionals
  scales_id: numbers
  units_id: currency
  decimals:
    min: 0
    max: 4
    default: 3

Example of setting decimal places for the whole dataset:

decimals:
  min: 0
  max: 5
  default: 0
decimal_separator:
  value: .
thousands_separator:
  value: ' '