Inputted values for Table Calculations

calculations
low_priority
done
reply
(Juan Restrepo) #1

I have a column with different currencies, and another column where I want to consolidate these currencies into one (say, USD?). The exchange rates change daily, and the primary consumer of a dashboard I built requested having a box where they could input the exchange rates, which would consolidate the values into new column automatically. Presently, I “hard coded” these exchange rates, and I update them periodically (once a week for the time being) so it reflects current exchange rates. The box idea sounds useful, and even though I have no other examples at hand where this could help, I believe it could be useful in more circumstances.

0 Likes

(DCL) #2

Hi Juan,

Great question! There are a few ways to approach this and there are certainly tradeoffs for each.

I would say the most automated and thorough way to provide exchange rates to a destination currency would be to pull in exchange rates through an API for example into your database(see possible sources here). This would provide you with an up to date exchange rate table in your DB from which users could simply define the destination currency – USD for example – and get the appropriate equivalent from each currency in your mixed column (assuming there is a way to identify the currency for each value there)

If investing that kind of dev time up front to bring exchange rates into your ETL process isn’t an option, you could use templated filters – this would involve making a filter field for each currency in your mixed column (users would input exchange rates to USD here on the front end) and create your USD dimension with a CASE statement using the values input into those filters to convert to USD.

I tried to just give a rough idea of each approach here, please let us know if one of these routes sounds better than the other and I’m happy to provide more detail for you.

Best,

Jiro

0 Likes

(Ben) #3

I have a similar question to this original one but I’m not exactly sure how to apply your advice to my use case.

What I am trying to accomplish is essentially the same as the original post - I have a table of values, and I want to convert those values to a dollar value based on another user-inputted value. There is no table I can reference to convert the values to dollars so it has to be a user-putted solution.

Is that something I can do with a parameter? It looks like parameters will only accept whole integer values. Is there any way to accomplish the same if the user provides a value with decimals, like 15.50 or 12.25?

Thanks.

0 Likes

(Izzy) #4

I’m not seeing this same limitation with parameters. A parameter of type: number seems to be able to accept any number I throw at it, making the conversion steps you’re after work pretty well.

   parameter: test_int_param {
    type: number
  }
0 Likes

(Ben) #5

Perhaps my misinterpretation then. Can you point me toward any documentation that would help me to understand how to implement this with a parameter and table calculation then?

0 Likes

(Izzy) #6

Ooh, I did not read the actual topic of this thread and just replied to your message. Parameters and table calculations don’t play together— You can only use parameters to take user input and feed them into an actual dimension or measure.

But, if it must be a table calc, you could have a setup like this (though it’s a bit convoluted, and I think getting the values into a table like Jiro suggested would be the best solution for sure):

parameter: the_param {
type: number
}
#The dimension just exists to bring the parameter value into the explore 
#in a way that a table calc can reference it
dimension: pass_through {
type: number
sql: {% parameter the_param %} ;;
}

and then a table calc could be just

${field_to_convert}*${pass_through}

We don’t have any official documentation of using parameters with table calcs since it’s not usually done, but the parameter docs are generally instructive for syntax:

0 Likes