Count total of 1s, 2s, 3s, 4s, and 5s in single expression

[Anonymous]

Hello,

I am trying to count survey results to display them in a pie chart.

I want to count the number of 1s, 2s, 3s, 4s, and 5s in the field [Type of PII] in one expression.

I am able to do it in multiple expressions but this will not allow me to show the values on the pie chart. 

Please let me know if you know the syntax to make this work in 1 expression.

Thank you!

[swuehl]

Maybe

=Count({<[Type of PII] = {'1s','2s','3s','4s','5s'}>} [Type of PII])

[Colin-Albert]

Can you set [Type of PII] as your dimension and then count([Type of PII]) as the expression?

[Anonymous]

Hi, thank you for the response.  This did not work unfortunately.

[Anonymous]

Worked, thank you!

[Anonymous]

Is it possible to change the values in the legend.  That is, if I want "1" to represent some sort of text?

[swuehl]

Maybe use a calculated dimension like

=Pick( [Type of PII],

Text1,

Text2,

Text3,

Text4,

Text5

)

Would be even better to map the values in the load script.

[Colin-Albert]

You can use a dual functionto hold both numeric and text data in the same field.

QlikView Addict: QlikView Functions: dual()

[Anonymous]

In the script.  How would I rename multiple values in a field.

i.e. 1 = General, 2 = private in field 'Type of PII'?

[swuehl]

There are multiple options. You can use a MAPPING table and ApplyMap() function or with few value to map maybe just like

LOAD

     [Type of PII],

     Dual(Pick( [Type of PII] , 'General','private'), [Type of PII] ) as [Type of PII Text],

     ...

FROM ...;