Restrict and combine data

[#1]

Hi All
I am trying to build a chart like below --

Product Country
T & F J & K L & O & Y

A & B 123 423 442
C & D & E 324 534 563
F & G 123 5521 634

There are several products and countries records but this chart should restrict the data only to few of those. Also the data is grouped based on custom criteria.

For example, for 2 seperate products - A & B and 2 seperate countries - T & F, the amount is - 123

Can someone give me a direction to build a chart like this? Appreciate any help..

Thanks in advance!

[fernandotoledo]

Man, in QV 8.5 "What´s New" example they show a way to create this kind of comparissons using set analisys. But it would be limited to a pre defined number of Customer or Country groups per chart;

[#3]

Thanks.. I looked into Whats New example that was shipped with QV 9.x but couldnt find any chart that is similar to what I am trying build... can you tell me which chart object you are referring to?

I Understand that I need to use Set Analysis for this type of chart, but its more than that like I also have to combine different data sets into single column..

Am trying out few options, but if someone has done this type of chart before, I would appreciate if I can get any pointers..

[johnw · Accepted solution]

I don't think I'd use set analysis. I'd just build the groups and use the groups in a pivot table as the dimensions, and sum(Amount) as the expression. Products and countries that aren't in a group will automatically be excluded from the chart if you set suppress when null on the dimensions.

[Data]:
LOAD
chr(ord('A')+floor(rand()*10)) as Product
,chr(ord('F')+floor(rand()*20)) as Country
,ceil(rand()*100) as Amount
AUTOGENERATE 100
;
LEFT JOIN ([Data])
LOAD
subfield("Product Group",' & ') as Product
,"Product Group"
INLINE [
Product Group
A & B
C & D & E
F & G
];
LEFT JOIN ([Data])
LOAD
subfield("Country Group",' & ') as Country
,"Country Group"
INLINE [
Country Group
T & F
J & K
L & O & Y
];

[#5]

Thanks much! This is what I was looking for!