AWS Quicksight - question about creating a calculated field using if else and custom aggregation

Question

I have a data that looks like this

Date	Name	SurveyID	Score	Error
2022-02-17	Jack	10	95	Name
2022-02-17	Jack	10	95	Address
2022-02-16	Tom	9	100
2022-02-16	Carl	8	93	Zip
2022-02-16	Carl	8	93	Email
2022-02-15	Dan	7	72	Zip
2022-02-15	Dan	7	72	Email
2022-02-15	Dan	7	72	Name
2022-02-15	Dan	6	90	Phone
2022-02-14	Tom	5	98	Gender

I wanted to have a segmentation data using the avg. score per individual.

Segment
A:  98%-100%
B:  95%-97%
C:  90%-94%
D:  80%-89%
E:  0% -79%

I did an if else formula which is this:

ifelse(Score} >= 98,'A',ifelse({Score} >= 95,'B',ifelse({Score} >= 90,'C',ifelse({Score} >= 80,'D','E'))))

This is now the output of what I did:

Date	Name	SurveyID	Score	Error	Segement
2022-02-17	Jack	10	95	Name	B
2022-02-17	Jack	10	95	Address	B
2022-02-16	Tom	9	100		A
2022-02-16	Carl	8	93	Zip	C
2022-02-16	Carl	8	93	Email	C
2022-02-15	Dan	7	72	Zip	E
2022-02-15	Dan	7	72	Email	E
2022-02-15	Dan	7	72	Name	E
2022-02-15	Dan	6	90	Phone	C
2022-02-14	Tom	5	98	Gender	A

I realized that the calculation I did only applies for the score. I was expecting an output like this:

Name	Average Score	Total Survey	Segement
Jack	95	1	B
Tom	99	2	A
Carl	93	1	C
Dan	81	2	D

I have tried to create another calculated field for Average Score which is:

avgOver({Score}, [Name], PRE_AGG)

I believe I am missing a distinct count of survey IDs in that formula, that I do not know where to place. As for segmentation calculation, I cannot on my life figure that part out without getting aggregation errors on Quicksight. Please help, thank you.

Answer 1

Got the answer from Quicksight Community. Pasting it here.

For segmentation, you can use the calculated field which you created for average score .

avg_score = avgOver(Score,[Name],PRE_AGG)

Segment

ifelse
(
    {avg_score}>= 98,'A',
    {avg_score}>= 95,'B',
    {avg_score}>= 90,'C',
    {avg_score}>= 80,'D',
    'E'
)

The survey id can be used to get the distinct count per individual.