topic Re: CALCULATED FIELD IN PIVOT in QlikView

CALCULATED FIELD IN PIVOT

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: CALCULATED FIELD IN PIVOT

sunny_talwar — Mon, 05 Jun 2017 15:20:02 GMT

May be an expression like this

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

Re: CALCULATED FIELD IN PIVOT

Anonymous — Wed, 07 Jun 2017 09:54:32 GMT

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

Re: CALCULATED FIELD IN PIVOT

sunny_talwar — Wed, 07 Jun 2017 12:55:57 GMT

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

Re: CALCULATED FIELD IN PIVOT

Anonymous — Mon, 19 Jun 2017 09:23:07 GMT

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

Re: CALCULATED FIELD IN PIVOT

sunny_talwar — Mon, 19 Jun 2017 09:34:17 GMT

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

Re: CALCULATED FIELD IN PIVOT

Anonymous — Mon, 19 Jun 2017 09:53:34 GMT

Oops yeah I just realized it.

Here is the attached sample file for your reference.

Thanks,

Ritesh

Re: CALCULATED FIELD IN PIVOT

sunny_talwar — Mon, 19 Jun 2017 12:10:57 GMT

Does this look like what you wanted?

Re: CALCULATED FIELD IN PIVOT

Anonymous — Mon, 19 Jun 2017 12:49:23 GMT

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

How do I write this script and where ?

Thanks,

Ritesh

Re: CALCULATED FIELD IN PIVOT

sunny_talwar — Mon, 19 Jun 2017 12:53:17 GMT

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

Re: CALCULATED FIELD IN PIVOT

Anonymous — Thu, 22 Jun 2017 11:34:20 GMT

Hello Sunny,

This doesn't seem to be working for me. Please refer to the attached file.

I am unable to use the file you have shared as I am using personal edition. Please help !

Thanks,

Ritesh

Re: CALCULATED FIELD IN PIVOT

sunny_talwar — Thu, 22 Jun 2017 11:41:24 GMT

Two things

1) Use just one expression here

2) Enable partial sub totals for your 2nd dimension

After those 2 changes, I get this

Re: CALCULATED FIELD IN PIVOT

varunamin — Thu, 07 May 2020 22:54:31 GMT

Hi Sunny, Your post was very helpful. Using that I was able to create one calculated column in pivot table but how can I show 2 calculated columns in pivot table. Please let me know if that's possible. Thanks!