9 Replies Latest reply: Dec 15, 2015 2:24 PM by Stefan Wühl

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

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!

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Maybe

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

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

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

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

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

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Worked, thank you!

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

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

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

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.

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

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

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

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

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

[Type of PII],

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

...

FROM ...;

• ###### Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

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