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!
Best answer by Ulku Kijasev
View original