Question: Use QlikView to compare year over year on line chart

[kevincagle]

First off, I am new to Qlikview. I have some of the basic navigation down but not very familiar with the operation. It is a large program with so much power available. Honestly its a little intimidating, but all things are when you first jump into it. I look forward to learning this application from all sides.

I have a question that I cant seem to find an answer to (that doesn't involve the scripting which I haven't gotten into yet). I have an excel spreadsheet with 10,000+ rows with a similar format to the example below.

Example Spreadsheet shortened : Color for Year 2011 changed for easy identification of year.

Year	Month	Location	Shelf	Category	SubCat	Volume
2010	Jan	Rack 1		Prime Color	Red	12
2010	Jan	Rack 1		Prime Color	Blue	15
2010	Jan	Rack 1		Color	Green	4
2010	Jan	Rack 1		Color	Orange	6
2010	Jan	Rack 1		Color	Purple	77
2010	Jan	Rack 1		Prime Color	Yellow	1
2010	Jan	Rack 1		Color	Black	12
2010	Jan	Rack 1		NonColor	White	15
2010	Feb	Rack 2		Prime Color	Red	4
2010	Feb	Rack 2		Prime Color	Blue	6
2010	Feb	Rack 2		Color	Green	77
2010	Feb	Rack 2		Color	Orange	6
2010	Feb	Rack 2		Color	Purple	1
2010	Feb	Rack 2		Prime Color	Yellow	5
2010	Feb	Rack 2		Color	Black	4
2010	Feb	Rack 2		NonColor	White	48
2011	Jan	Rack 1		Prime Color	Red	16
2011	Jan	Rack 1		Prime Color	Blue	115
2011	Jan	Rack 1		Color	Green	114
2011	Jan	Rack 1		Color	Orange	16
2011	Jan	Rack 1		Color	Purple	177
2011	Jan	Rack 1		Prime Color	Yellow	12
2011	Jan	Rack 1		Color	Black	112
2011	Jan	Rack 1		NonColor	White	115
2011	Feb	Rack 2		Prime Color	Red	116
2011	Feb	Rack 2		Prime Color	Blue	418
2011	Feb	Rack 2		Color	Green	522
2011	Feb	Rack 2		Color	Orange	366
2011	Feb	Rack 2		Color	Purple	17
2011	Feb	Rack 2		Prime Color	Yellow	96
2011	Feb	Rack 2		Color	Black	81
2011	Feb	Rack 2		NonColor	White	19

I need to make a line chart that shows the change in Volume for Category, organized by Rack (shelf not as important), for the Year 2011 listed month by month compared to Year 2010.

I can set a line graph that shows one or 2 of the dimensions I need but cant figure out how to expand so it will show the other dimensions.

realize there may be a limit to whether Rack can show month to month over 2 years factoring Category in a standard line graph. In this scenario, would Combo Chart be a feasible option to generate the desired results? How would I set the varying dimensions?

I'm not asking someone to do it for me because I prefer to learn how its done. If someone does make it for me I ask that an explanation of how to do it be included with it.

Thank you for any guidance provided.

Kevin.

PS: My apologies if this is posted in the incorrect sub-forum. New to this style of forum.

[shiveshsingh]

You can put two expression, one for 2011 and other for 2010 ..for calculating the Volume.

In dimension, u can set the required dimension.

[kevincagle]

Im not sure I understand how, but here is what's coming to mind.

Properties-->Expressions-->Add:

Year(2011)

-->Add:

Year(2010)

Or is there more to it?

Then I would need to do what in the dimensions properties?

Im wrong or messed up somewhere but it seems Dimensions only factors the top 2 entries, of which I currently have Month, Location, Year, Category set in that order.

[shiveshsingh]

Expressions like,

sum({<Year = {'2010'}>}Volume)

sum({<Year = {'2011'}>}Volume)

[shiveshsingh]

May be this

[kevincagle]

Unfortunately I cannot view your file. QlikView Personal limitations in effect.

[shiveshsingh · Accepted solution]

use this expression

sum({<Year = {'2010'}>}Volume)

sum({<Year = {'2011'}>}Volume)

and add dimension as Category and Month