Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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))
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)
)
Or transform your data using CROSSTABLE LOAD prefix
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