The simplified data model include 2 tables
In the example, Comparison countries of US are China and Japan, and Comparison countries of Germany are UK, FR and IT.
Other countries don't have Comparison countries.
What I am trying to show in the Chart is,
If select a country with Comparison countries, then show selected Country Name, Amount of selected country, Amount summary of Comparison countries of selected country;
If select a country without Comparison countries, then show selected Country Name, Amount of selected country, zero
|Country Name||Amount of selected country||Amount summary of Comparison countries of selected country|
Please find attached QVW for data. Thanks in advance, and sorry about my terrible english.
load * inline [
COUNTRY_NAME as MapCountry,
AMOUNT as MapAmount
load * inline [
ApplyMap('FACTMap',COUNTRY_NAME_COMPSET,0) as COMP_VALUE
drop Table COMPSET;
I have added a Mapping Load to create the COMP_VALUE field. This field can then be easily incorporated into your application.
I hope that this helps
Hi Rupert Cavendish,
Thanks for reply.
The pre-calculate method works for the simplified data model, but actually I got more dimensions such as Date, City and so on in the FACT table.
I want to calculate the amount of comparison countries on the fly.
I tried set analysis, see new chart named as "Set Analysis" in attached, but it's not working as I want.
For example, when select US, I want the chart show 1 rows,
US for country name
1000 for amount of US
1700 for amount summary of China and Japan,
but I got 3 rows instead.
If I remove country name from the chart, I got the summary of comparison countries, but I have to show country name in the chart.
What I think you are looking for is hierarchical. I have put together a hierarchical structure in the attached application that I think will help as it adds up each of the lower levels automatically.
Please let me know if this is any good