8 Replies Latest reply: Nov 1, 2017 10:23 AM by Sunny Talwar RSS

    Set Analysis Set Identifier

    Josh Pinfold

      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

        • Re: Set Analysis Set Identifier
          Sunny Talwar

          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))

            • Re: Set Analysis Set Identifier
              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))

                • Re: Set Analysis Set Identifier
                  Josh Pinfold

                  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

                    • Re: Set Analysis Set Identifier
                      Sunny Talwar

                      What do you mean by KPI Result Here?

                        • Re: Set Analysis Set Identifier
                          Josh Pinfold

                          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

                            • Re: Set Analysis Set Identifier
                              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])