topic Crosstab in QlikView

Crosstab

Tue, 21 Dec 2010 12:17:55 GMT

Hello All,

I'm looking for the structure below on my sheet (crosstab)

X	Calendar Year 2009	Calendar Year 2010
A	$30	$40
B	$20	$48
C	$5	$10

My raw data structure is like

Calendar_Year	X	Rev
2009	A	5
2009	B	10.053
2011	A	5
2011	C	7
2012	A	8
2012	B	40

so if I use a straight table with SUM for Rev I 'm not able to get cross tab structure as above, please suggest how that can be achieved aprt from pivot as pivot will also show me but in vertical direction.

is it possible from object properties only or do I need to change the raw data structure using transformation. if I can do from object property that be great as change in structure could loss my other elements in dashboard.

Thanks..

Crosstab

deepakk — Tue, 21 Dec 2010 12:39:15 GMT

Hi,

Once you have done with SUm(Rev), pull the year dimension to the top of the Rev.

Drag it to the top and you will get your data in cross format.

Crosstab

Tue, 21 Dec 2010 13:02:49 GMT

Thanks Deepak, would you please explain, pull the year dimension on to top means promote or demote in a straight table.

Crosstab

mazacini — Tue, 21 Dec 2010 16:08:07 GMT

Pivot table can be used to show in desired format:

In Dimensions: X and Calendar Year

In Sort - make sure X is first dimension.

In presentation - show partial sum on X

This will give you a pivot table in format piv1 per attached.

Click on + to display Calendar Year (piv 1a)

Drag Calendar Year to top - gives desired result in piv 2 attached

Crosstab

mazacini — Tue, 21 Dec 2010 16:10:18 GMT

Sorry about the layout in the previous post. The images are in sequence 1, 1a and 2