Pivot table - Dynamic values

[#1]

Hi,

How to display the values dynamically in pivot table based on condition. if value is 0 then 'Yes' and if value is 1 then 'No'.

Please help

[stigchel]

You can use an expression to do that

If(Answer=0,'Yes',if(Answer=1,'No','N/A')

But it would be better to do this in load script

MyMap:

Mapping Load * inline [Answer,Label

0,'Yes'

1,'No'

];

Load

Applymap('MyMap',MS1,MS1) as MS1,

Applymap('MyMap',MS2,MS2) as MS2

...

From Your Data

[#3]

I already have a expression in Dimensions and Expressions tab

[sasikanth]

Here MS1,Ms2,MS3....

these are dimensions or what?

[#5]

Hi,

MS1,Ms2,MS3.... are the dimensions (Mystery Shopper)

[sasikanth]

Then try this at script level

if(Ms1='0', 'yes',if(Ms1='1','No','N/A)) as Ms1,

if(Ms2='0', 'yes',if(Ms2='1','No','N/A)) as Ms2,

.

.

.

then use in your table

[#7]

Hi Sasikanth,

Thanks for your reply.

However i am confused that if i do this is script level then how will i get the percentage values in pivot table.

I am doing the percentage calculation in expression.

if(SecondaryDimensionality()=1, sum(Alt(Value,0)), num(sum (Alt(Value,0))/count (MYSTERY_SHOPPER ),'0%') )

Thanks,
Selva

[stigchel]

For the first part of your if, you don't need a sum  (there is but 1 interview, I'm I right ??) and you could do the

if(Value=0,'Yes',if(Value=1,'No'),'N/A')

For the second part you now calculate the % of No answers (if you explained the 0,1 values right) You might want to calculate the Yes answers either in number or text form by doing:

Sum(if(Value=0,1,0)/Count(TOTAL <Question> Value)

or

Sum(if(Value='Yes',1,0)/Count(TOTAL <Question> Value)

[#9]

Hi,

I tried this

if(Value=1,'Yes' , if (Value=0,'No', if(SecondaryDimensionality()=1, sum(Alt(Value,0)),  num(sum (Alt(Value,0))/count (MYSTERY_SHOPPER ),'0%') ) ))

I need to display the percentage even if all answers are N/A or No

Please help.

[stigchel]

The Dimensionality check still needs to be first:

if(SecondaryDimensionality()=1,if(Value=1,'Yes' , if (Value=0,'No', sum(Alt(Value,0)),  num(sum (Alt(Value,0))/count (MYSTERY_SHOPPER ),'0%') ) ))