Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis Result in another Set Analysis

I have a First Sorted Value calculation which generates a 3 letter code for a SurveyLocation and is on the dashboard as a KPI. The First Sorted Value expression uses set analysis to generate the highest scoring SurveyLocation. I would then like to use this result in another set analysis in a table so only the comments from this top performing SurveyLocation show.

The top performing location is generated by;

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

and  I would like it to appear in the SurveyLocation part of the following;

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

  Touchpoint = {'Bag Drop'},

    SurveyLocation = {Result Here}>}

[Insight Sentiment])

Any help would be really appreciated

1 Solution

Accepted Solutions
sunny_talwar

Or this

Avg(

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

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=SurveyLocation = FirstSortedValue(TOTAL 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])

View solution in original post

2 Replies
sunny_talwar

How about 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])

Added dollar sign expansion to my previous response here

Set Analysis Set Identifier

sunny_talwar

Or this

Avg(

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

    Touchpoint = {'Bag Drop'},

    SurveyLocation = {"=SurveyLocation = FirstSortedValue(TOTAL 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])