Pivot Table and calculated fields

[Anonymous]

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.

[swuehl]

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

[Anonymous]

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

[swuehl]

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

[Anonymous]

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

[swuehl]

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

[Anonymous]

Hi Stefan,

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

[Anonymous]

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