Pivot table issue

[#1]

Hi All,

I am using the below expression

if ( SecondaryDimensionality()=1 ,

if ( sum(Alt(Value,0)) = 1 ,    Dual('Yes',1) ,  if ( sum(Alt(Value,0)) = 0 , Dual('No',0) ,  sum(Alt(Value,0)))) ,

   num(sum ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE2'}>}Alt(Value,0))/

  count ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE2'}>}  Value ),'0%'  ))

i have values 1,0,null in my DB for columns Q1M,Q2,Q3...Q13. I need to display if value =1 then Yes, if value =0 then No and if value=null then N/A.

I cannot do this in script since calculation is involved.

Below is my script:

Crosstable(Questions, Value,4)

SQL Select MYSTERY_SHOPPER, DEALER, COUNTRY, WAVE, Q1, Q2, Q3, Q4, Q5,Q6,Q7A,Q7B,Q8,Q9,Q10,Q11,Q12,Q13,

Q14,Q15A,Q15B,Q15C,Q15D,Q15F,Q15G,Q15H,Q15I,Q16,Q17,Q18,Q19,Q20,Q21,

Q22,Q23,Q24,Q25,Q26,Q27,Q28,Q29,Q30,Q31,Q32_A ,Q32_B,Q32_C,Q32_D,Q33,Q34,Q35,Q36,Q37,Q38,Q39,Q40,Q41,Q42,Q43,Q44,Q45,

Q46,Q47,Q48,Q49,Q50,Q51,Q52,Q53,Q54

From XX_JLR_SALES_DATA_ENTRY_NEW WHERE ENABLED='YES';

Pivot table Dimension :

Questions

Mystery_Shopper

In Chart properties -> Presentation -> Uncheck "Supress Zero-Values"

For example for Q9,

Mystery_Shopper1=1

Mystery_Shopper1=0

Mystery_Shopper1=Null

Then pivot table display

Yes, No, N/A

Mystery_Shopper1=Null

Mystery_Shopper1=Null

Mystery_Shopper1=Null

As above if all the values are null, Then pivot table is not displaying Q9.

Please find the attached my QV file.

Appreciate if some one help me.

Thanks,

Selva

[#2 · Accepted solution]

Hi All,

Finally I find out the solution.

I updated the null value to -1 and filter the values 1 and 0 in expression.

if ( SecondaryDimensionality()=1 ,

if ( sum(Alt(Value,0)) = 1 ,    Dual('Yes',1) ,  if ( sum(Alt(Value,0)) = 0 , Dual('No',0), if ( sum(Alt(Value,0)) = -1 , Dual('N/A',0) , sum({<Value={'1','0'}>}[Value])))),

num(sum ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE2'},Value={'1','0'}>}Alt(Value,0))/

count ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE2'},Value={'1','0'}>}  Value ),'0%'  ) )

Thanks.