Average with distinct values to be used in a parameter?

robdhondt · July 13, 2022, 8:42pm

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: