Solved

Linking two unrelated datasets in MAQL

  • 18 June 2021
  • 3 replies
  • 231 views

Hello,

I am attempting and failing to write some MAQL queries.

I have two datasets with the following fields (model simplified from the real world case, which has many more attributes for contracts):

  • Contracts
    • Vendor name
    • Contract currency
    • Contract value in contract currency
  • Exchange Rates
    • Currency
    • Date
    • Rate

The Exchange Rates are stored as a rate between the respective currency and our company main currency. The exchange rate for the main currency being stored as 1.

I need to build a report which shows list of contracts (by vendors) with several metrics, including contract value:

  • in our company base currency
  • in the currency selected in a dropdown selector of the dashboard (the exchange rate for the current date should be used for conversion)

I have been trying to build the metrics, but I have not succeeded yet. I have started with creating a metric which would get the current day exchange rate for each contract:

SELECT
SUM(
SELECT SUM(Rate)
BY Date, Currency ALL OTHER
)
BY ALL OTHER
EXCEPT Currency
WHERE Date = THIS

This far it works. The result is that on each line I get a SUM of all the stored exchange rates for the current day. However, the next step fails. My (SQL based) thinking says that I somehow need to join the Contracts and Exchange Rates datasets on Contract Currency = Currency and this step fails and I get the message saying:

“The numbers in the report cannot be broken down by all of the selected attributes (including attributes specified within a metric definition)”

I also think that perhaps it is a bad data model design to even have two different “currency” attributes and not have them represented only once with references in the 2 datasets, but changing the data model is most likely not an option at the moment.

I would highly appreciate any help to overcome this.

Thanks!

icon

Best answer by Ulku Kijasev 20 July 2021, 16:19

View original

3 replies

Userlevel 3

Hello Allan,

I will start from the very end. Yes, the best scenario would be to have just one Currency Attribute within your model.

I have to admit, it is bit difficult to answer your questions correctly without seeing your model, despite your detailed description.
Are these two datasets connected at least indirectly via different dataset, please?
If so, you can try to use the BY clause on the level of the Primary key of the related dataset. Just be aware, the BY ALL OTHER clause overrides all attributes and might affect the metric computation incorrectly.

This is quite complex MAQL, but we can try to assist you. However, the rebuilding of the model might be really needed here.

Hi Iva,

Thank you for your response! Yes, the datasets were not connected at all. So I actually implemented a few data model changes in my development environment and I think I have some good progress. I created a new dataset “Currencies” and created reference column into both “Exchange rates” and “Contracts” dataset. Now I was able to create a metric that finds the respective exchange rate for the contract currency and is able to convert the contract currency to our company base currency. The metric for this is:

SELECT
SUM(Contract Annual Amount) /
(
SELECT SUM(Rate)
BY Date, Currency Code
ALL OTHER
WHERE Date = THIS
)

However, I am now struggling with my second step of the conversion. How can I create a dropdown in the dashboard which would allow me to choose the currency for the second conversion (from company base currency to the selected currency)? If I just add a currency filter in my dashboard, then it does not behave as a destination currency selector, but would instead act as a filter on which contracts the report will show. For example, if I select EUR in the dropdown, then I would only see the contracts that are recorded with EUR as their currency. My goal is that all the contracts would be visible, but the values should be shown in the selected currency.

Do you have any suggestion how to achieve this?

Best wishes,

Allan

Hi Allan,

In order to implement your second step conversion in GoodData, which is converting the contract amount to some target (destination) currency, you can create another Target Currency Dataset. Your model will look like something below:

Then you can change your metric as below: (You can give Date as current date as you have given previously) because you need to also filter the rate according to your target currency.

SELECT SUM(Contract Amount)/( SELECT SUM(Rate) BY Date (CurrencyDate), Currency Id, Target Currency Id ALL OTHER WHERE Date (CurrencyDate)=06/01/2021 )

After that, you can create your insight to include the metric above (Contract_amount_in_other_cur)
And in a dashboard you can add this insight and in the dashboard filter you can choose the Target Currency which will apply the correct exchange rate to the contract Amount.
In my sample "Sum Of Contract Amount" is the original Contract Amount and Contract_amount_in_other_cur is the contract amount in target currency which is chosen in the dashboard filter.

currency.csv and target_currency.csv will have similar content as below

currency_id,currency
1,"EUR"
2,"USD"
3,"CZK"

In the exchange_rates you need to upload all rates (from source currency to target currency) as below:

exchange_rates.csv
currency_id,target_currency_id,date,rate
1,3,"2021-06-01",25.61
2,3,"2021-06-01",21.77
3,3,"2021-06-01",1
1,3,"2021-07-01",24.00
2,3,"2021-07-01",20.05
3,3,"2021-07-01",1
1,2,"2021-06-01",5.61
2,2,"2021-06-01",3.77
3,2,"2021-06-01",7
1,2,"2021-07-01",12.25
2,2,"2021-07-01",13.06
3,2,"2021-07-01",2.02

For ex: If you need to convert all all contract_amounts to target currency USD, then in dashboard filter you will choose Target_currency=USD and Contract_amount_in_other_cur metric will apply exchange rates from above dataset where currency_id=contract_currency_id and target_currency=2 and date= 01-06-2021 (in my case, but you can use current date also)find out the corresponding contract_amounts in USD.

Since i don’t know your business needs, i tried to work on a test model. I hope you can use this template for your use case.

Best Regards,

Ulku Kijasev

 

 

Reply