Set Analysis Set Identifier

[Anonymous]

I have the following First Sorted Value expression as a KPI which returns the airport with the highest average score over the last week. I would like it to remain fixed when applying filters on other fields. I cannot seem to add the {1} set identifier anywhere to get this to work;

FirstSortedValue(distinct

{<Feedback_Day_Start={"<=$(=date(max(Feedback_Day_Start)-1))>=$(=date(max(Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count(Score) >= 10"},

    SurveyLocation = {"=len(SurveyLocation)=3"}>}

SurveyLocation, -Aggr(Avg(

{<Feedback_Day_Start={"<=$(=date(max(Feedback_Day_Start)-1))>=$(=date(max(Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count(Score) >= 10"},

    SurveyLocation = {"=len(SurveyLocation)=3"}>}

Score), SurveyLocation))

Thanks

[sunny_talwar · Accepted solution]

Try this

FirstSortedValue(distinct

{1<Feedback_Day_Start={"<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count({1}Score) >= 10"},

    SurveyLocation = {"=len(Only({1} SurveyLocation))=3"}>}

SurveyLocation, -Aggr(Avg(

{1<Feedback_Day_Start={"<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count({1}Score) >= 10"},

    SurveyLocation = {"=len(Only({1} SurveyLocation))=3"}>}

Score), SurveyLocation))

[sunny_talwar]

Further you might want to combine the two set analysis for SurveyLocation

FirstSortedValue(distinct

{1<Feedback_Day_Start={"<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count({1}Score) >= 10 and len(Only({1} SurveyLocation))=3"}>}

SurveyLocation, -Aggr(Avg(

{1<Feedback_Day_Start={"<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count({1}Score) >= 10 and len(Only({1} SurveyLocation))=3"}>}

Score), SurveyLocation))

[Anonymous]

Thank you Sunny. Its worked perfectly. I also have a table where I would like the set analysis to work according to the result of the above formula.

avg(

{<Feedback_Day_Start={"<=$(=date(max(Feedback_Day_Start)-1))>=$(=date(max(Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {KPI Result Here}>}

[Insight Sentiment])

Can you help? Thanks

[sunny_talwar]

What do you mean by KPI Result Here?

[Anonymous]

The result of the above formula is a 3 letter airport code which shows the highest ranking airport. I would then like the table below to only show values corresponding to this airport. The airport field is 'SurveyLocation' but it is not possible to copy it into the avg calculation.

avg(

{<Feedback_Day_Start={"<=$(=date(max(Feedback_Day_Start)-1))>=$(=date(max(Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {'FirstSortedValue(distinct

{1<Feedback_Day_Start={"<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count({1}Score) >= 10 and len(Only({1} SurveyLocation))=3"}>}

SurveyLocation, -Aggr(Avg(

{1<Feedback_Day_Start={"<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=Count({1}Score) >= 10 and len(Only({1} SurveyLocation))=3"}>}

Score), SurveyLocation))

}>}

[Insight Sentiment}0

[sunny_talwar]

May be this

Avg(

{<Feedback_Day_Start={"<=$(=date(max(Feedback_Day_Start)-1))>=$(=date(max(Feedback_Day_Start)-7))"},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=FirstSortedValue(distinct

{1<Feedback_Day_Start={""<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))""},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {""=Count({1}Score) >= 10""},

    SurveyLocation = {""=len(Only({1} SurveyLocation))=3""}>}

SurveyLocation, -Aggr(Avg(

{1<Feedback_Day_Start={""<=$(=date(max({1}Feedback_Day_Start)-1))>=$(=date(max({1}Feedback_Day_Start)-7))""},

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {""=Count({1}Score) >= 10""},

    SurveyLocation = {""=len(Only({1} SurveyLocation))=3""}>}

Score), SurveyLocation))"}>}

[Insight Sentiment])

[Anonymous]

That unfortunately returns a null value. What is the use of the double quotations?

[sunny_talwar]

It is used as an Escape sequences