Users want to select dynamically different country currencies and want to see total Price amount for that Specific Currency

Q&A
, ,
1

I have uploaded 2 datasets, where Main table and lookup table. Here in main table, I my all-price amounts in USD already converted. But user expecting us to design report where he wants to see his over all sales in different currency rates as per latest date. By default it will be in USD, already converted to USD price amount but to convert from USD to GBP or AUD or EUR, then we have first joined the Main table and Lookup table using USD currency and made inner join/Left outer join. After this created parameter and controls to select required currencies from single select parameter and linked this to calculated fields as below:

First calculated field as below: SelectedExchangeRate
Sum(ifelse(${SelectedCurrencyCode}=‘USD’, 1,
${SelectedCurrencyCode}=‘AUD’, {ExchangeRate},
${SelectedCurrencyCode}=‘CAD’, {ExchangeRate},
${SelectedCurrencyCode}=‘EUR’, {ExchangeRate},
${SelectedCurrencyCode}=‘GBP’, {ExchangeRate},
1
)
)

Also created Second calculated field as below: FilteredExchangeRate

ifelse(ToCurrency = ${SelectedCurrency}, ExchangeRate, null)ifelse(ToCurrency = ${SelectedCurrency}, ExchangeRate, null)

Third Calculated field: LoanAmountInSelectedCurrency
PriceAmountUSD * coalesce(FilteredExchangeRate, 1)

Finally moved the main table dims and these above calculated fields to table, this logic is only for USD amount but dynamically not working for other country currencies, please working on this POC to showcase clients and bring quick sight project to our company, please help?

Please consider my request as urgent and help me, am very new to quick sight as well.

2

Hi @veerah , welcome to Quick Sight community. What is your datasource, number of rows in your main table and the lookup table? There are multiple ways you can approach the use case:

  1. If you only have four currencies as listed above, you can create a lookup table with a single row having different currencies and then use it within an IFELSE clause above where you can replace the {ExchangeRate} with the selected currency column
  2. Use direct query with custom SQL and dataset parameters
3

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!

1 Like