topic Re: Adding pivot table dimentions together in QlikView

Adding pivot table dimentions together

Tue, 04 Sep 2012 13:47:15 GMT

Hi,

I cant seem to do something I thought would be quite simple.

I want to do caluclations on 2 coulmns within a pivot table

Month	Year	2011	2012
Jun		80	41
May		118	52
Feb		19	41
Aug		48	18
Nov		44	-
Jul		44	47
Apr		23	66
Oct		60	-
Mar		14	58
Dec		34	-
Sep		57	-
Jan		7	38

I would like a column at the end that can work a percentage out. This is quite straightforward in excel, but cant work out how to do it in QlikView

Any help would be great.

I currently have 2 dimentions, Month and Year, and 1 expression which is COUNT(ClientID)

Cheers

Re: Adding pivot table dimentions together

Anonymous — Tue, 04 Sep 2012 14:18:29 GMT

There are a few ways to do this, you can simply write "Column(2)/Column(3)" to divide the second column with the third, this is a bit unstable if you start moving columns around or in this case you add more years...

You can also use Set Analysis to compare different values with each other.

COUNT({$<Year={$(=Max(Year)-1)}>} ClientID) /COUNT({$<Year={$(=Max(Year))}>} ClientID)

This would divide 2011 with 2012 (if you had more years then you would compare the max value of year with the previous year ie. 2012 - 1 = 2011 or if you selected 2011 then it would be 2011-1 = 2010). Check out the help or

comunity for more info on Set Analysis.

Re: Adding pivot table dimentions together

whiteline — Tue, 04 Sep 2012 15:46:15 GMT

I'll show the secret:

Headers:

LOAD distinct

Year

Year as YearHeader

Resident SomeTable;

Concatenate(Headers)

LOAD

'Percentage' as YearHeader

Autogenerate (1);

Then use YearHeader instead of Year as dimension.

Correct your expression so that for YearHeader = 'Percentage' it returns the desired percentage.

Re: Adding pivot table dimentions together

Tue, 04 Sep 2012 15:47:57 GMT

Hi,

Would you be able to attach an example. I've tried doing it and read help files, but if I put another expression in I get 2 per year and the nex expression just has a '-'.

Cheers

Re: Adding pivot table dimentions together

whiteline — Tue, 04 Sep 2012 15:52:22 GMT

Did you modify the script and create YearHeader ?

Re: Adding pivot table dimentions together

Tue, 04 Sep 2012 15:59:32 GMT

Hi,

You posted that as I was posting mine.

However the script has confused me a bit as I already a bit of manipulating my data in the script.

The Year is taken from a date so in my load script i do:

TEXT

(date(RTTDate, 'YYYY')) as Year

Cheers

Re: Adding pivot table dimentions together

whiteline — Tue, 04 Sep 2012 16:08:40 GMT

Add the lines that I suggested above.

Replace 'SomeTable' with your table name from where you copied the string to your post.