5 Replies Latest reply: Aug 28, 2013 8:46 AM by Aldea Cosmin

Stack bar using a dimension

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

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

Thanks!

• Re: Stack bar using a dimension

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

Crosstable(Year, Sales, 2)

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

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

• Re: Stack bar using a dimension

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