Hi, thanks for your response.
Almost. The problem is that I have a list of currencies and I managed to map currencies to specific countries, but in case of EUR I would like to be able to map it to more than one country.
I know I should be able to do it the other way around, having list of countries and corresponding currencies in different dataset, but in our database we only keep a currency. Do you see any way to achieve it?
So, you're right, manually mapping countries to currencies is one way to do this. In the example I attached, I created groups for a few countries -- Australia, France, Germany, and the US. Australia and US are both the only countries in their group, but France and Germany are both included in the EUR group. This will probably work for you but will be quite a bit of work depending on how many countries you have.
What I originally tried but was unsuccessful at was joining a csv file with Country Name and Currency as my columns and then grouping countries based on the joined field currency. That would probably be a better solution, but I don't know if it's possible.
Currency.twbx.zip 689.8 KB
I thought grouping countries would be good, but unfortunately it's not the data I have. What I need isn't perfect either.
What would be totally perfect if I could map my dimension (Sales Region) to group of countries on the map. That would open totally new possibilities for me.
Let's say I have a data that is marked as BL and I want it to be shown across Netherlands, Luxembourg and Belgium..
Back to the subject. I could potentially export all countries and include them in the groups, that shouldn't be a problem. Let's say I make an excel spreadsheet where:
Netherlands, Belgium, Luxembourg | Benelux | EUR
Australia, Tuvalu, Kiribati | Australia | AUD
how can I then use it to map my data which looks like:
number of sales | currency
to the map?
Wow, that is pretty much I need, I'll study over this workbook to make sure I grasp the idea
I managed to create excel with countries and their currencies. However, generated Latitude and Longitude is grey on Measures List?
My connection looks like following:
1. SQL Server table
a) currency code
b) number of sales
2. Excel Spreadsheet
b) currency code
I've connected 1a + 2b but what next?
edit: if I manage to put longitude on columns shelf and latitude on rows shelf I'm getting "Invalid field formula due to limitations in the data source"
Did you join them or just edited relationship? If you see two data sources in your workbook, select that on which has country names on it and drag country name into Detail shelf and then take sales from other source. I guess you are now using that source as a main source which don't have country information...
Just start with that sheet1. Now you are using sheet1 as a secondary source (red mark over there).
I did that, however my database connection got blue tick as soon as I added it (after the sheet)
Select a new worksheet and take the first variable from sheet1 to your worksheet. First variable defines which one is primary source.
Great stuff, almost there!
One last thing is not to repeat the label for each country, but a currency - so for example EUR label is only once, not for all Euros countries. Can this be done?