I'm working on a project in Qlik Sense where I'd like to be able to calculate a Z-score for various metrics. The difficulty I am having is in calculating a standard deviation for CSAT from survey responses, aggregated by vendor. The data I have is transactional, so I have to first calculate a CSAT score for each vendor using a count of positive survey responses divided by total responses by vendor, which is easy enough. The difficulty I'm having is being able to use those calculated values to get a standard deviation for scores aggregated by vendor rather than individual scores for each transaction. In order to do what I need to with this project, these calculations need to be dynamic, because there are filters for date, location, etc in the application I'm working in. Below is a simplified example of the table I have to work with.
| TransactionNumber |
Vendor |
Date |
Zip |
SatisfactionScore |
| 1 |
A |
5/14/2021 |
33194 |
3 |
| 2 |
B |
12/21/2021 |
85530 |
4 |
| 3 |
B |
11/3/2021 |
29336 |
3 |
| 4 |
C |
8/6/2021 |
43250 |
3 |
| 5 |
A |
3/20/2021 |
21972 |
4 |
| 6 |
D |
5/25/2021 |
88778 |
5 |
| 7 |
C |
8/20/2021 |
89437 |
1 |
| 8 |
A |
7/21/2021 |
99204 |
2 |
| 9 |
D |
3/17/2021 |
32727 |
4 |
| 10 |
E |
3/19/2021 |
70849 |
1 |
| 11 |
E |
10/24/2021 |
63873 |
4 |
| 12 |
C |
9/1/2021 |
40799 |
5 |
| 13 |
B |
12/28/2021 |
18517 |
4 |
| 14 |
A |
1/24/2021 |
67699 |
3 |
| 15 |
E |
3/21/2021 |
58763 |
3 |
| 16 |
D |
6/5/2021 |
64479 |
4 |
Here is the aggregation I need
| |
Positive Response |
Total Response |
CSAT |
| A |
1 |
4 |
25.00% |
| B |
2 |
3 |
66.67% |
| C |
1 |
3 |
33.33% |
| D |
3 |
3 |
100.00% |
| E |
1 |
3 |
33.33% |
And I need to be able to calculate a standard deviation for the calculated CSAT percentages, in this example, 31.4%.
Thanks in advance,
Nate
