Concat and If to return a list of possible results

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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 · Accepted solution]

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