Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Hi, Please help me with this Chart

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 NameAmount of selected country Amount summary of Comparison countries of selected country
US10001700
China9000
Germany7001200

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

6 Replies
Not applicable
Author

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
Specialist
Specialist

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

Not applicable
Author

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.

Not applicable
Author

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
Specialist
Specialist

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

Not applicable
Author

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