topic Re: How to create a chart from multiple columns without crosstab in QlikView

How to create a chart from multiple columns without crosstab

Tue, 08 Jul 2014 17:47:34 GMT

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,

Re: How to create a chart from multiple columns without crosstab

maxgro — Tue, 08 Jul 2014 18:09:30 GMT

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

Re: How to create a chart from multiple columns without crosstab

Gysbert_Wassenaar — Tue, 08 Jul 2014 18:37:36 GMT

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.

Re: How to create a chart from multiple columns without crosstab

rwunderlich — Tue, 08 Jul 2014 18:40:18 GMT

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

Re: How to create a chart from multiple columns without crosstab

Wed, 09 Jul 2014 08:16:31 GMT

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

Re: How to create a chart from multiple columns without crosstab

rwunderlich — Wed, 09 Jul 2014 18:40:17 GMT

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

-Rob