select multiple Country to make comparison.

[Architect]

I have an excel containing data for 60 countries and each of them having multiple rows.

What I want is to select any 2 countries -

1>  and that will show 2 different line chart , Each chart having  Month in 'Y' axis, Transaction_volume in 'X axis.'

2> also there will be one table that will give Average Difference in 'Volume' and 'Value' between those selected countries.

[Digvijay_Singh]

Would be great if you can share sample data!

[Architect]

Below is a dummy data list. think we have data for 12 months for each countries.

COUNTRY	MONTH	TRAN_VAL	TRAN_VOL	NO OF CLIENT	LOB
INDIA	JAN-23	1000	20	5	R
JAPAN	DEC-23	12000	30	7	G
INDIA	MAR-23	20000	25	4	C
UAE	AUG-23	2500	40	8	R

[Rohan]

Hi,

1> Line Chart : Month as group & Country as Lines. Transaction Volume as your measure.

Then you have to do 2 things,

Fisrt : Appearance > Presentation > select vertical.

Second : Data Handling : Calculation Condition : Getselectedcout(Country)=2.

2> Pivot Table : 'Avg Diff' as Dimension.

Vol : Avg(aggr(sum({<Country={"$(=subfield(concat(distinct Country,','),',',1))"}>}Vol),Month)) - Avg(aggr(sum({<Country={"$(=subfield(concat(distinct Country,','),',',2))"}>}Vol),Month))

Value : 

Avg(aggr(sum({<Country={"$(=subfield(concat(distinct Country,','),',',1))"}>}Value),Month)) - Avg(aggr(sum({<Country={"$(=subfield(concat(distinct Country,','),',',2))"}>}Value),Month))

Data Handling : Calculation Condition : Getselectedcout(Country)=2.

Try this out once.

Regards,

Rohan.

[Architect]

@Rohan  I need to create two different line charts side by side to show the comparison, not into the single chart.

When no country is selected , the blank space will give some disclaimer.

second solution- yet to check.

[Rohan]

Hi @Architect , 

Create 2 line charts with only Month as dimension & use the measures like :

CountryA : sum({<Country={"$(=subfield(concat(distinct Country,','),',',1))"}>}Vol)

CountryB : sum({<Country={"$(=subfield(concat(distinct Country,','),',',2))"}>}Vol)

This will give you 2 seperate charts. & the data handling part will be the same.

Regards,

Rohan.