What would be the best way to calculate Total count of a specific value across multiple fields

[Anonymous]

Person	Day	Rating 1	Rating 2	Rating 3	Rating 4	Rating 5
Person A	Day 1	Excellent	Satisfactory	Satisfactory	Satisfactory	Satisfactory
Person A	Day 2	Satisfactory	Satisfactory	Excellent	Unsatisfactory	Satisfactory
Person A	Day 3	Satisfactory	Satisfactory	Satisfactory	Unsatisfactory	Satisfactory
Person B	Day 1	Satisfactory	Satisfactory	Excellent	Excellent	Unsatisfactory
Person B	Day 2	Unsatisfactory	Unsatisfactory	Satisfactory	Excellent	Satisfactory
Person B	Day 3	Satisfactory	Unsatisfactory	Unsatisfactory	Satisfactory	Satisfactory

This is my data.  I want to count the number of Excellent, Satisfactory and Unsatisfactory values across all days for each person.  So, I would end up with:

Person	Excellent	Satisfactory	Unsatisfactory
Person A	2	11	2
Person B	3	7	5

And, of course, I would love to be able to drill down on the chart to find out which ratings had 2 Excellent for Person A, etc.

What is the best way to calculate and display this information?

[Gysbert_Wassenaar]

Perhaps with these expressions:

rangesum(count({<[Rating 1]={'Excellent'}>}[Rating 1]),count({<[Rating 2]={'Excellent'}>}[Rating 2]),count({<[Rating 3]=

{'Excellent'}>}[Rating 3]),count({<[Rating 4]={'Excellent'}>}[Rating 4]),count({<[Rating 5]={'Excellent'}>}[Rating 15))

rangesum(count({<[Rating 1]={'Satisfactory'}>}[Rating 1]),count({<[Rating 2]={'Satisfactory'}>}[Rating 2]),count({<[Rating 3]={'Satisfactory'}>}[Rating 3]),count({<[Rating 4]={'Satisfactory'}>}[Rating 4]),count({<[Rating 5]={'Satisfactory'}>}[Rating 15))

rangesum(count({<[Rating 1]={'Unsatisfactory'}>}[Rating 1]),count({<[Rating 2]={'Unsatisfactory'}>}[Rating 2]),count({<[Rating 3]={'Unsatisfactory'}>}[Rating 3]),count({<[Rating 4]={'Unsatisfactory'}>}[Rating 4]),count({<[Rating 5]={'Unsatisfactory'}>}[Rating 15))

[maxgro]

sum(aggr(SubStringCount(

          Left([Rating 1],1)&Left([Rating 2],1)&Left([Rating 3],1)&Left([Rating 4],1)&Left([Rating 5],1),

          'U'          /* or E or S  for other expressions */

     ), Person, Day)

)

[swuehl]

Or transform your data using CROSSTABLE LOAD prefix

The Crosstable Load

CROSSTABLE( RatingNo, Rating,2)

LOAD Person,

    Day,

    [Rating 1],

    [Rating 2],

    [Rating 3],

    [Rating 4],

    [Rating 5]

FROM

[https://community.qlik.com/thread/248507]

(html, codepage is 1252, embedded labels, table is @1);

Which makes dimensions / expression trivial and allows to drill in