Set Analysis expression - issue with exclusion of concatenated string values

[surendra_masupa]

Hi All,

I'm struggling to write a set analysis expression to find the excluded value of strings.

To give the background, I've a list of people and cities where they operate. And I would write an expression where they won't operate.

I've written expression:

Concat( {$<AllCities-={$(=concat(DISTINCT chr(39)&OperatingCity&chr(39),','))}\>}DISTINCT AllCities,',') which works fine only when an agent is selected, else it returns null.

***Operating City = Combination of all the cities which I've cross joined to all the agents.

Pls help me regarding this.

Thank you!

[sunny_talwar]

I believe set analysis is evaluated one per chart and that is why it isn't working, try this:

Concat(DISTINCT If(AllCities <> OperatingCity, AllCities),',')

My bad, this isn't completely working

[surendra_masupa]

Hi Sunny,

This is not completely correct. When there are two or more operating cities for an agent, the result is incorrect.

Pls see the highlighted.

[effinty2112]

Hi surendra,

How about :

=concat({$<AllCities -= {$(=Only(Aggr(concat(DISTINCT OperatingCity,', '), Agent)))}>}DISTINCT AllCities,', ')

This will work in a textbox when one agent is selected. It's more difficult to get an equivalent expression that would work in a straight table.

cheers

Andrew

[surendra_masupa]

Nope, doesn't work.

The output is same as the one with my expression.

[Anil_Babu_Samineni]

Like this

Concat( {$<AllCities-={$(=concat(DISTINCT chr(39)&OperatingCity&chr(39),','))}\>}DISTINCT AllCities,',')

What is the use of Back slash there.

[effinty2112 · Accepted solution]

Hi Surendra,

          Try this:

Agent	Operating Cities	Concat(Aggr(if(index(Concat(TOTAL <Agent> OperatingCity,'|'),AllCities) =0, AllCities), Agent, AllCities),', ')
Keiren	Kolkata	Bangalore, Chennai, Delhi, Mumbai
Martin	Chennai, Delhi	Bangalore, Kolkata, Mumbai
Peter	Bangalore, Mumbai	Chennai, Delhi, Kolkata
Ryan	Mumbai	Bangalore, Chennai, Delhi, Kolkata

[surendra_masupa]

Thank you Andrew.