CALCULATED FIELD IN PIVOT

[Anonymous]

Hi,

I am fairly new to Qlikview and am looking for your help in fixing a challenge with pivot table. I have created a pivot table in one of my reports and now I need to create a calculated field.  Sample -

COUNTRY	PROMOTERS	DETRACTORS	PASSIVES
CHINA	2002	2020	2038
AUSTRALIA	2036	2054	2072
UK	2070	2088	2106
USA	2104	2122	2140

Where Promoter, Detractors & Passives are values of one of the fields. What I essentially need is to create a new calculated column that should be  (Promoter - Detractors) / Sum(Promoter, Detractors & Passives).

Any help will be really appreciated.

Thanks,

Ritesh

[sunny_talwar]

May be an expression like this

(Sum({<Field = {'Promoter'}>}Value) - Sum({<Field = {'Detractors'}>}Value))/Sum(Value)

[Anonymous]

Hello Sunny,

Thanks for your reply, but this doesn't seem to be working. Its creating 3 additional columns for the calculated expression and there is no value in those columns.

Thanks, Ritesh

[sunny_talwar]

Would you be able to share an app where you are trying this?

[Anonymous]

Hello Sunny,

I am using personal edition of Qlikview, thus cant share the file with you. Please refer to the screenshot attached here. On the right is the Data that I am using and on the left is the pivot that I want to create along with a calculate field as mentioned below.

Thanks,

Ritesh

[sunny_talwar]

First of all, if you are using personal edition of QlikView how does it matter if you share your qvw file? If I have a licensed version, I can still open your file.

And secondly, can you provide an Excel instead of giving us snapshot because I cannot directly load data from snapshot.

Thanks,

Sunny

[Anonymous]

Oops yeah I just realized it.

Here is the attached sample file for your reference.

Thanks,

Ritesh

[sunny_talwar]

Does this look like what you wanted?

[Anonymous]

Wow Sunny!! Yes that's exactly what I want.

How do I write this script and where ?

Thanks,

Ritesh

[sunny_talwar · Accepted solution]

All I did was enable partial sum for SENTIMENT dimension and used dimensionality to show a different expression for the total column

If(SecondaryDimensionality() = 0, (Count({<SENTIMENT = {'PROMOTER'}>}[CUSTOMERS NUMBER]) - Count({<SENTIMENT = {'DETRACTOR'}>}[CUSTOMERS NUMBER]))/Count([CUSTOMERS NUMBER]), Count([CUSTOMERS NUMBER]))

Read about dimensionality here:

How to use - Dimensionality()

The second dimension... or how to use secondarydimensionality()