How to create a chart from multiple columns without crosstab

[#1]

Hi all,

I'd be hugely grateful if you could offer me some advice.

I have a database with a list of customers- its one row per customer, and they all each have lots of columns with headings such as gender, age etc. Obviously, presenting these demographics in charts is a simple procedure of using the heading as a dimension.

However, there is also a column per product that can be bought, and for each customer row it has the number of that product they have bought. For example:

Customer         Age     Gender       Socks     Scarves     Hats     Gloves

A                       20         M              10             4            1          0        

B                       32          F                2              0            4          2 

C                       28          F                0              4            0          8     

I would like to present this in a bar chart where  each product has its own bar, displaying the sum of all sales of that product.

Given that you can't have multiple dimensions on bar charts, the only way I have found to do this is with crosstab. However, I want to maintain the data's current granularity, so that we can filter properly, and in both directions. E.g. how many women bought scarves, and what is the gender split of people who bought scarves. Is this possible?

Any help that could be provided would be extremely appreciated.

Thanks,

TP

[maxgro]

if you use a crosstab in the load (I suggest this way) you simplify the chart, the table should be

Customer, Age, Gender, Product, Sales

if you don't want to modify the table, maybe you can find some help in the attachment

[Gysbert_Wassenaar]

Personally I would use the crosstable function to transform the data:

Sales:

Crosstable(Product, Amount,3)

LOAD Customer, Age, Gender, Socks, Scarves, Hats, Gloves

From ...salessource...;

The resulting table will look like:

Customer	Age	Gender	Product	Amount
A	20	M	Gloves	0
A	20	M	Hats	1
A	20	M	Scarves	4
A	20	M	Socks	10
C	28	F	Hats	0
C	28	F	Socks	0
C	28	F	Scarves	4
C	28	F	Gloves	8
B	32	F	Scarves	0
B	32	F	Gloves	2
B	32	F	Socks	2
B	32	F	Hats	4

This makes it a lot easier to work with. See attached qvw.

[rwunderlich]

You can make a bar chart with

- No Dimension

- An expression for each product column e.g.  sum(Socks)

-Rob

http://masterssummit.com

http://robwunderlich.com

[#5]

Thanks very much to everybody for your responses.

With regards to Rob's method, is there a way of presenting the legend as labels on the horizontal axis instead (as if each category was a dimension)? The only way I can think of would be to remove the legend and manually place text boxes for each product.

Thanks

[rwunderlich]

Just uncheck "Show Legend" on the Presentation tab and the Label values will display below the bars.

-Rob