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

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.

CodePudding user response：

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.