I sent it to Looker support as well but I was wondering if anybody has similar requirement.
I have 30+ measures that come from an aggregation table in an original currency. Instead of creating two extra fields per measure with suffixes _eur
and _usd
I decided to do the following.
Create parameter to allow people to choose whether they want to see converted values:
parameter: conversion_currency {
type: unquoted
default_value: "1"
allowed_value: {
label: "Euro"
value: "EUR"
}
allowed_value: {
label: "US Dollars"
value: "USD"
}
}
And then based on this parameter I select a field from the table. Each row has both FX rates already available.
dimension:fx_rate {
sql: {% if conversion_currency._parameter_value == "EUR" %}
${TABLE}.fx_rate_eur
{% elsif conversion_currency._parameter_value == "USD" %}
${TABLE}.fx_rate_usd
{% else %}
1
{% endif %} ;;
}
Here is a sample measure:
measure: net_value {
type: sum
sql: ${TABLE}.net_value / ${fx_rate} ;;
value_format_name: decimal_2
}
When no currency conversion is selected it’s defaulting to net value / 1
, which is great. The problem I’m having is to apply specific formatting depending on the parameter.
As far as I know I can’t use parameters in definition of my own format but it’s basically what would solve the problem
named_value_format: currency_dynamic {
value_format: {% if conversion_currency._parameter_value == "EUR" %}
"eur_2"
{% elsif conversion_currency._parameter_value == "USD" %}
"usd_2"
{% else %}
"decimal_2"
{% endif %}
}
and then I could use
measure: net_value {
value_format_name: currency_dynamic
}
Which means if I had to add new currency conversion, I need to only change it in 3 places and all measures would work automatically. Any ideas how to achieve right now?
The only thing I saw as possible solution was:
html:
{% if conversion_currency._parameter_value == "EUR" %}
€{{ rendered_value }}
{% elsif conversion_currency._parameter_value == "USD" %}
${{ rendered_value }}
{% else %}
{{ rendered_value }}
{% endif %};;
But I can’t remember where but I think I saw somebody having pointed out some shortcomings with this approach. Also this is a lot of code to repeat , not sure if I can put this into another parameter or a constant but then again constant can’t be defined in a view file