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

Concat and If to return a list of possible results

Wondering how I can combine a concat and an if expression. I have a concat expression which lists all the subsubcategories;

     concat(distinct SubSubCategory, ', ')

I also have a table with 'subsubcategory' as the dimension and the following expression which returns the subsubcategory name when the conditions are met;

     if($(SentimentChange)>0, SubSubCategory)

How can i combine the two so that I can product a list of all the subsubcategories which meet the conditions of the if statement. The following don't work;

     concat(if ($(SentimentChange)>0, distinct SubSubCategory), ',')

     concat(if distinct ($(SentimentChange)>0, SubSubCategory), ',')

     concat(distinct if ($(SentimentChange)>0, SubSubCategory), ',')

Thanks

1 Solution

Accepted Solutions
sunny_talwar

So, then you basically have an aggregation over another aggregation (Concat over Avg)... in which case you need to add Aggr() function here

Concat(DISTINCT Aggr(If($(SentimentChange) > 0, SubSubCategory), SubSubCategory), ',')

View solution in original post

3 Replies
sunny_talwar

How do you define SentimentChange? Is this a variable containing expression?

Anonymous
Not applicable
Author

yes SentimentChange is a variable which takes the average sentiment score (1-10 numeric) for the last 7 days using a set analysis and subtracts the average sentiment of the preceeding 7 days;

avg({<Feedback_Day_Start={"<=$(=date(max(Feedback_Day_Start)-1))>=$(=date(max(Feedback_Day_Start)-7))"}>}[Insight Sentiment])

-

avg({<Feedback_Day_Start={"<=$(=date(max(Feedback_Day_Start)-8))>=$(=date(max(Feedback_Day_Start)-14))"}>}[Insight Sentiment])

sunny_talwar

So, then you basically have an aggregation over another aggregation (Concat over Avg)... in which case you need to add Aggr() function here

Concat(DISTINCT Aggr(If($(SentimentChange) > 0, SubSubCategory), SubSubCategory), ',')