I have a list of items with prices in different currencies, each item in a class which is used as the dimension. I want to display the totals by class in a selected display currency. And I want to do this with (A) the current selection {$}, and (B) the entire set {1}.
The load statements are as follows:
Data:
LOAD * INLINE [
Class,Item,Price,Currency
'Tech','TV',200,'GBP'
'Tech','VCR',100,'EUR'
'Tech','Speakers',400,'USD'
'Home','Sofa',800,'GBP'
'Home','Table',900,'EUR'
'Home','Rug',200,'USD'
'Garden','Swing',150,'GBP'
'Garden','Parasol',250,'GBP'
'Garden','Mower',300,'GBP'
];
Currencies:
LOAD * INLINE [
Currency,ExchangeRate
USD, 1.00
EUR, 0.90
GBP, 0.65
];
DisplayCurrencies:
LOAD * INLINE [
DisplayCurrency,DisplayRate
USD, 1.00
EUR, 0.90
GBP, 0.65
];
My expressions are respectively:
(A): DisplayRate*Sum(Price/ExchangeRate)
(B): DisplayRate*Sum({1}Price/ExchangeRate)
When I select different combinations of Class the first chart behaves as expected, displaying the selected dimensions.
But the second chart changes in synch with the first whereas I expect it to show all dimensions all the time.
If I remove the scalar DisplayRate from the second expression to become
Sum({1}Price/ExchangeRate)
then it displays all classes all the time, as I expect.
Is there some subtlety in set expressions that I have missed?
Point taken! And I like the idea of setting a variable to the value from the data island to ensure it is a scalar. That is the point of my island in the first place. I am still getting my head around the associative data model as opposed to the relational third normal form I have used all these years!
