Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Pivot table - filter in expression

Hi All,

I have questions Q1 to Q33. i need to display only Q1 to Q13 in a pivot table.

Script, dimensions and expressions used are as below

Script

Table:

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

From Table1

Dimension

1. =if (Match(Questions,'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'),Questions)

2. MYSTERY_SHOPPER

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'}>}Alt(Value,0))/

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

filter.png

Based on the filter in expression i am supposed to get Q1 to Q13.  i don't know why i am getting the the row before total.

Please help to avoid the row before total.

Thanks,

Selva

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Pivot table - filter in expression

This:

Dimension

1. =if (Match(Questions,'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'),Questions)

returns Q1 - Q13 for Questions which are in the match

and null() for Queststions that aren't

These null-values are shown in the last line.

Suspress null values in the dimension tab.

View solution in original post

2 Replies
Highlighted
Partner
Partner

Re: Pivot table - filter in expression

This:

Dimension

1. =if (Match(Questions,'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'),Questions)

returns Q1 - Q13 for Questions which are in the match

and null() for Queststions that aren't

These null-values are shown in the last line.

Suspress null values in the dimension tab.

View solution in original post

Highlighted
Not applicable

Re: Pivot table - filter in expression

Thanks a lot MartinPohl