Average with distinct values to be used in a parameter?

risk33 · July 13, 2022, 1:50pm

I have a dataset 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

The formula for the calculate field I use before is: I name this calculated field as “Avg. Score”

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

This is giving incorrect average due to the duplicates on my dataset. I changed the calculate field to this:

SUM({Score}/ countOver({Score}, [{Survey ID}], PRE_AGG) )
/
DISTINCT_COUNT({Survey ID})

This new formula gave me the right average. However, I am unable to use this calculated field in my parameter as it gives me error that I cannot use a pre-filter/pre-agg values.

Now below are the formulas I have for my parameter calculated field. What I’m trying to achieve is a toggle that will allow end users to play with the data. It answers what if the X segment is able to hit a score of Y, what will my average look like, something like that. This is the photo for reference.

Formula for Simulated Avg. Score:

ifelse
(
    {Segment} = 'A',
     ifelse(${A}= 'Yes', ${NewScore}, {Avg. Score}),
    {Segment} = 'B',
     ifelse(${B}= 'Yes', ${NewScore}, {Avg. Score}),
    {Segment} = 'C',
     ifelse(${C}= 'Yes', ${NewScore}, {Avg. Score}),
    {Segment} = 'D',
     ifelse(${D}= 'Yes', ${NewScore}, {Avg. Score}),
    {Segment} = 'E',
     ifelse(${E}= 'Yes', ${NewScore}, {Avg. Score}),
     {Avg. Score}
)

{NewScore} Parameter is just a control that is an integer type with a static value of 98. This is the “Change Score To” on the screenshot.

I have created string parameters from Segment A to E for the “Yes or No” toggle. {A} to {E}

This is the calculated field I have for the {Segment} parameter.

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

The initial formula I have for ‘Avg. Score’ seem to work fine with the simulated avg. score calculated field.

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

Now that I changed this to this formula below, I am unable to use this field due aggregated values. How do I solve this? Please help. Thank you.

SUM({Score}/ countOver({Score}, [{Survey ID}], PRE_AGG) )
/
DISTINCT_COUNT({Survey ID})

robdhondt · July 13, 2022, 8:42pm

Hi @risk33 -

You are almost there. You are right that aggregations and LAC-A functions can’t be used to create a dimension.

Here is a workaround you can use using LAC-W functions.

Step 1 - Create a composite key for survey responses. This is optional but I’m doing it to keep things organized.
c_survey_response_key

// create a composite key for survey response (surveyid + name)
concat(tostring(SurveyID),Name)

Step 2 - Create a weighted score for each survey response
c_survey_weighted_avg_score_LAC-W

// find the weighted score
Score/countOver(Name, [{c_survey_response_key}], PRE_AGG)

Step 3 - Create a calculated field for the weighted avg
c_survey_weighted_avg_score_LAC-W

// find the weighted avg (total of weighted scores) / (# of survey responses)
sumOver({c_survey_weighted_score_LAC-W}, [Name], PRE_AGG)
/
distinctCountOver({c_survey_response_key}, [Name], PRE_AGG)

Step 4 - Now you can use c_survey_weighted_avg_score_LAC-W for your segment dimension
c_survey_score_segment

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

Example:

risk33 · July 14, 2022, 5:26pm

Hi @robdhondt, thank you so much for taking the time to check this.

I have tried this and was able to get the correct average overall. However, when I try to have a segmented view of the average score, it only gave me a single segment which is C as the value 91.33 is for the whole data set. Is there a way where I can show the average per segment A through E? I was hoping to have an output like this that I can visualize on a chart.

Thank you so much!

robdhondt · July 14, 2022, 5:42pm

Hi @risk33 -

I have good news, the recently released LAC-A functions make this super easy. You couldn’t use these before because your calculation needed to be used as a dimension. With an average you don’t have that problem.

You can create an additional calculated field and group at the level you need. Name, Survey, and Segment.

avg(avg(Score,[Name,SurveyID,{c_survey_score_segment}]))

risk33 · July 14, 2022, 7:20pm

Thank you @robdhondt, I have tried this calculated field but unfortunately it gave me an error.

avg(avg(Score,[Name,SurveyID,{c_survey_score_segment}]))

robdhondt · July 14, 2022, 7:47pm

Hi @risk33 -

Those are the field names I used for the example I shared with you. Did you update them to the ones for your analysis?

risk33 · July 14, 2022, 7:53pm

Yes, I have updated it but it is showing that error.

robdhondt · July 14, 2022, 8:16pm

Can you share your calculated field and the error details in “Show details”?

risk33 · July 14, 2022, 8:34pm

The error I get is “Please contact Quicksight team to solve this issue”

avg(avg({Eval Score},[Advocate,{Eval Number},{a_segment}]))

robdhondt · July 15, 2022, 1:59am

Hi @risk33 -

Thank you for the information. There is an error caused by including the segment in the calcuation.

After researching the good news is I don’t think you need it.

You can use:

avg(avg(score,[name,surveyid]))

This is because the inner avg is the LAC-A function which calculates the average at the name and survey level. The outer avg will then avg across your segment.

Since you want the segments by Name you need to update the original c_survey_weighted_avg_score_LAC-W as well. (I’ve edited the original post)

// find the weighted avg (total of weighted scores) / (# of survey responses)
sumOver({c_survey_weighted_score_LAC-W}, [Name], PRE_AGG)
/
distinctCountOver({c_survey_response_key}, [Name], PRE_AGG)

risk33 · July 15, 2022, 1:08pm

This works! I just tried it. Awesome, appreciate all the help @robdhondt

risk33 · July 15, 2022, 4:28pm

Hi @robdhondt , I have one more question I have tried this calculated field for a parameter calculation but I am getting an error. I am hoping this is possible.

avg_score

avg(avg(score,[name,surveyid]))

I have tried this on an if else calculated field but have gotten an error.

ifelse
(
    Segment = 'A',
     ifelse(${A}= 'Yes', ${NewScore}, {avg_score}),
    Segment = 'B',
     ifelse(${B}= 'Yes', ${NewScore}, {avg_score}),
    Segment = 'C',
     ifelse(${C}= 'Yes', ${NewScore}, {avg_score}),
    Segment = 'D',
     ifelse(${D}= 'Yes', ${NewScore}, {avg_score}),
    Segment = 'E',
     ifelse(${E}= 'Yes', ${NewScore}, {avg_score}),
     {avg_score}
)

This is the error I get:

Would you happen to know a workaround for this? Thank you very much.

robdhondt · July 15, 2022, 4:48pm

Hi @risk33 -

You are running into the limitation where you can’t use LAC-A functions as dimensions.

I know the LAC-A make it super easy, we are working on making that possible. For now you have to use the LAC-W approach I shared, it does not have the dimension limitation.

risk33 · July 15, 2022, 4:50pm

Understood. Thank you for the information!