topic Pivot Table and calculated fields in QlikView

Pivot Table and calculated fields

Anonymous — Sat, 27 Aug 2011 20:51:38 GMT

Hi, I have a Pivot Table with two dimensions (projectname and projectreport) and about thirty expressions (single fields containing separate project costs values). I dragged the dimensions to the top and the expressions are placed to the left. Example:

Pivot Table	+ Projectname Z
	Projectreport X	Projectreport Y
Project costs field 1
Project costs field ...
Project costs field 30

Now I want to add a calculated column (for instance containing column(1) - column(2), percentages, ...).

Glad to get some advice on the best/easiest way to achieve this.

Regards, Sander.

Pivot Table and calculated fields

swuehl — Sat, 27 Aug 2011 22:36:49 GMT

Hi,

I think a solution for what you need was developed by John Witherspoon and posted here:

http://community.qlik.com/message/35586#35586

Stefan

Pivot Table and calculated fields

Anonymous — Sun, 28 Aug 2011 14:35:23 GMT

Hi, can't get it done this way. This would give me an additional row below the values of the already existing expressions. I need a column to compare the individual expressions from projectreport X with projectreport Y. And then again between projectreport Y and projectreport W. Etc... But I'd be happy with a comparing column between the two first columns only. Any ideas? Thx, Sander

Pivot Table and calculated fields

swuehl — Sun, 28 Aug 2011 14:52:09 GMT

If I understood your problem and John's solution correctly, the solution will add values to a dimension.

Since you pivot your dimensions to the top, in horizontal orientation, it should give you additional columns, not adding rows (In John's example, he has not pivoted the dimensions, the dimensions are to the left, in vertical orientation, giving additional rows).

In your expression, you will need to check the dimension value and pick an appropriate expression then, like in John's solution.

So I still think this could fit your needs.

Regards,

Stefan

Re: Pivot Table and calculated fields

Anonymous — Sun, 28 Aug 2011 16:37:09 GMT

HI, still could not get it done. Can you show me in attached qvw? Thx, Sander

Re: Pivot Table and calculated fields

swuehl — Sun, 28 Aug 2011 21:13:48 GMT

Hi Sander,

have a look at attached example.

Compared to John's example, using two dimension levels make things a little more complicated.

I added a custom dimension field within projects that calculates the difference between second and first Project2 in each report.

I also tried to add a custom dimension field at the report level, but don't have a clue what to use here. It might also be difficult accessing the values from here.

It might also be an idea to add the calculations for the custom dimensions in script level, i.e. adding them to your fact table.

Hope this helps,

Stefan

Re: Pivot Table and calculated fields

Anonymous — Sun, 28 Aug 2011 21:46:55 GMT

Hi Stefan,

Many thanks for your efforts! I think I'll have to solve this on script level indeed. Regards, Sander

Re: Pivot Table and calculated fields

Anonymous — Mon, 29 Aug 2011 20:12:54 GMT

Hi, stumbled upon another interesting (partial) solution here:

http://community.qlik.com/message/67136#67136

Still, I would think there must be an easier and more flexible way to add calculated columns to pivot tables?? Compare more than two columns, add %, ...

Attached editted qvw file as basic case again, with it's limitations.

Any other tricks out there? Gr., Sander