topic Re: Stack bar using a dimension in QlikView

Stack bar using a dimension

zagzebski — Tue, 27 Aug 2013 20:44:36 GMT

I have the following data and want to create a stack bar chart:

What I want is for the X-axis to be the month, the expressions (2013 Sales Per Member and 2012 Sales Per Member) to the be the bars side by side for each month. Then I want the other dimension (line of Business) to be stacked for each expression. Can't see what I am doing different from a few of the examples I looked at on this site - but I can't figure how to stack them.

Incurred Month	Line of Business	2013 Sales Per Member	2012 Sales Per Member
		296.76	316.61
Apr	Electronics	2.19	2.51
Jan	Electronics	2.76	2.53
Mar	Electronics	2.94	3.45
Feb	Electronics	3.45	3.15
Feb	Furniture	9.92	12.13
Mar	Furniture	10.09	12.65
Apr	Furniture	10.59	12.41
Jan	Furniture	12.77	12.31
Feb	Kitchen	57.48	63.01
Mar	Kitchen	59.01	67.60
Apr	Kitchen	60.88	58.68
Jan	Kitchen	64.69	66.18

Re: Stack bar using a dimension

Oleg_Troyansky — Wed, 28 Aug 2013 00:27:52 GMT

You are trying to make a chart that is both grouped and stacked - the two expressions side by side require a grouped chart, while the Line of Business require a stacked chart. This is not something you can implement in QlikView.

Even if it was possible, the visual effect would be really hard to process - you'd need to follow monthly trend, through comparing year by year and divided by Line of Business... Too complex...

You'll need to reduce the complexity by one dimension - either show a stacked chart of monthly trends by Line of Business, or show a grouped chart of Monthly Trends comparing Year over Year, but without the distribution by Line of Business.

best,

Oleg Troyansky

Re: Stack bar using a dimension

zagzebski — Wed, 28 Aug 2013 11:42:33 GMT

Thanks!

Re: Stack bar using a dimension

Wed, 28 Aug 2013 12:10:44 GMT

You can convert the field name of the sales fields into a dimension, using Crosstable

Crosstable(Year, Sales, 2)

LOAD * INLINE [

Incurred Month, Line of Business, 2013 Sales Per Member, 2012 Sales Per Member

Apr, Electronics, 2.19, 2.51

Jan, Electronics, 2.76, 2.53

Mar, Electronics, 2.94, 3.45

Feb, Electronics, 3.45, 3.15

Feb, Furniture, 9.92, 12.13

Mar, Furniture, 10.09, 12.65

Apr, Furniture, 10.59, 12.41

Jan, Furniture, 12.77, 12.31

Feb, Kitchen, 57.48, 63.01

Mar, Kitchen, 59.01, 67.60

Apr, Kitchen, 60.88, 58.68

Jan, Kitchen, 64.69, 66.18

];

Thus will result a table containing Month, Line of Business, Year and Sales.

Afterwards you can create a bar chart, having month and LoB as dimensions, sum(sales) as expression, and, of course, Stacked option selected within the Chart's Properties >> Style.

Cos

Re: Stack bar using a dimension

zagzebski — Wed, 28 Aug 2013 12:19:37 GMT

I am not familiar with the crosstable function. So this would need to be utilized in the script?

Re: Stack bar using a dimension

Wed, 28 Aug 2013 12:46:27 GMT

Yes. Crosstable is a transformation method used in the script in order to transpose your data