Hi, Please help me with this Chart

[#1]

The simplified data model include 2 tables

1. FACT table, it contain 2 field, COUNTRY_NAME as dimension, and AMOUNT as metric
2. COMPSET table, it record Comparison countries of a country.

   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

For example

Country Name	Amount of selected country	Amount summary of Comparison countries of selected country
US	1000	1700
China	900	0
Germany	700	1200

Please find attached QVW for data. Thanks in advance, and sorry about my terrible english.

[#2]

You can get first column -Amount of selected country as expression in a straight table and calculate it conditionally by condition on GetCurrentField(Comparison countries).

[Roop]

FACT:
load * inline [
COUNTRY_NAME,AMOUNT
US,1000
China,900
Japan,800
Germany,700
France,500
UK,400
Italy,300
];

FACTMap:

Mapping Load
COUNTRY_NAME as MapCountry,
AMOUNT as MapAmount
Resident FACT;


NoConcatenate
COMPSET:
load * inline [
COUNTRY_NAME,COUNTRY_NAME_COMPSET
US,China
US,Japan
Germany,France
Germany,UK
Germany,Italy
];

CompSetComplete:

LOAD *,
ApplyMap('FACTMap',COUNTRY_NAME_COMPSET,0) as COMP_VALUE
Resident COMPSET;

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

[#4]

Hi Guruprem Saginadham, Thanks for replay.

But I'm not following you. Can you please make an example chart in the QVW? Thanks a lot.

[#5]

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.

[Roop]

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

[#7]

Hi Rupert Cavendish, Thanks again. I will look into your method.