Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

CALCULATED FIELD IN PIVOT

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 -

   

COUNTRYPROMOTERSDETRACTORSPASSIVES
CHINA200220202038
AUSTRALIA203620542072
UK207020882106
USA210421222140

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

1 Solution

Accepted Solutions
sunny_talwar

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()

View solution in original post

12 Replies
sunny_talwar

May be an expression like this

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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.

Screenshot.jpg

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
Not applicable
Author

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?

Capture.PNG

Anonymous
Not applicable
Author

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

How do I write this script and where ?

Thanks,

Ritesh

sunny_talwar

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()