topic Pivot Variables in QlikView

Pivot Variables

Fri, 11 Mar 2011 09:02:16 GMT

Hi,

My dataset has this structure:

Year bank €

2010 a 1000

2010 b 500

2010 c 200

2009 a 1000

2009 b 500

2009 c 200

I want to prepare a report with pivot tables like that

2009 2010 2010-2009

a € €

b € €

c € €

How can I use the variable "year" like a column?

Thank you!

Pivot Variables

Miguel_Angel_Baeyens — Fri, 11 Mar 2011 10:12:06 GMT

Hello,

Try a generic load:

OriginalData:LOAD Year, bank, "€"FROM File.qvd (qvd); Data:GENERIC LOAD bank, Year, "€"RESIDENT OriginalData; DROP TABLE OriginalData;

Hope that helps.

EDIT: Create a new pivot table. "bank" as dimension and Sum("2010") and Sum("2009") as expressions. You will need to quote years, otherwise it will sum 2010 instead of the values of field "2010".

Pivot Variables

pljsoftware — Fri, 11 Mar 2011 16:22:54 GMT

Hi,

how to use variable "year" like a column? Explain, as dimension???

Pivot Variables

johnw — Sat, 12 Mar 2011 01:17:20 GMT

If I understand your question, you use "Year" as a column by using a pivot table, and dragging the "Year" dimension to the top of the chart.

However, that won't get you the difference between the two years. A typical approach for that would be set analysis:

Dimension = bank
2009 = sum({<Year={'2009'}>} Amount)
2010 = sum({<Year={'2010'}>} Amount)
2010-2009 = column(2) - column(1)

Pivot Variables

Wed, 16 Mar 2011 11:37:16 GMT

Hi,

Yes, as dimension

Pivot Variables

Wed, 16 Mar 2011 11:45:11 GMT

Correct! Thank you for the answer!

Pivot Variables

Wed, 16 Mar 2011 11:47:26 GMT

Thank you John! all it's ok and you've understood my answer!