8 Replies Latest reply: Aug 23, 2013 9:27 AM by Bruno Santos

# Set Analysis and concat

Hi all,

I have a chart expression:

sum({1<[Ordering Depot]={'\$(=concat(distinct Depot))'}>} [Number Orders])

but this always (incorrectly) returns 0

I am using the dollar-sign expansion so that it returns the results of the concat in single quotes as 'Depot' is a text field which may contain spaces.

If I create a chart and put any of these expressions into it:

=concat(distinct Depot)
\$(=Year(Today()))
\$(=concat(distinct Year(Today())))

they return the expected values but if I try

\$(=concat(distinct Depot))

it returns nothing.

I am obviously missing something, but what?

Any help appreciated.

Gordon

• ###### Set Analysis and concat

Hello Gordon,

My guess is that since concat has no delimitator, is concatenating one field after another, and since they are text fields, they need a precedeing and trailing comma taht concat does not add, so

`\$(=chr(39) & Concat(distinct Año, chr(39) & ',' & chr(39)) & chr(39))`

will do the trick.

Hope that helps!

• ###### Set Analysis and concat

Hi Miguel,

I understand what you are saying but I couldnt get it to work:

sum({1<[Ordering Depot]={\$(=chr(39) & Concat(distinct Depot, chr(39) & ',' & chr(39)) & chr(39))}>} [Number Orders])

I actually found 'only' works and here is my expression:

sum({1<[Ordering Depot]={'\$(=only(Depot))'}>} [Number Orders])

I am still intrigued why concat doesnt though.

Regards,

Gordon

• ###### Set Analysis and concat

Gordon,

I'm probably missing something also. Anyway, you may try setting a variable with the whole analysis, say vAnalysis

`'{' & chr(39) & Concat(distinct Depot, chr(39) & ',' & chr(39)) & chr(39) & '}'`

and then in your expression

`Sum({1< [Ordering Depot] = \$(vAnalysis) >} [Number Orders])`

Might work. I also agree that John's expression might work and is cleaner by far, though.

Regards.

• ###### Set Analysis and concat

Hi Miguel/John,

Miguel - again nice thinking but the expression editor sees it as an error. I also tried changing the variable and removed the curly brackets and put them around the variable in the expression. The editor was happy this time but again nothing was returned.

John - the elemental function P() works great and one that I wasnt aware of.

Thanks for your inputs.

Regards,

Gordon

• ###### Re: Set Analysis and concat

Hi Guys,

I've a related problem.

Set Analysis restrict months depending on other expression.

Could you give me some help, with your expertise?

Thanks

Bruno

• ###### Set Analysis and concat

I think you should be able to use another field as an element set:

sum({1<[Ordering Depot]=Depot>} [Number Orders])

• ###### Set Analysis and concat

Hi John,

Thanks for your input.

I agree it should be as simple as that, but I found that it also returned 0 and that started me off on the road to concat. Again I dont know why it doesnt work.

Regards,

Gordon

• ###### Set Analysis and concat

`gordon.savage wrote:I agree it should be as simple as that, but I found that it also returned 0 and that started me off on the road to concat. Again I dont know why it doesnt work.`

Weird. Maybe this?

sum({1<[Ordering Depot]=P(Depot)>} [Number Orders])

But it sounds like you have a working solution already. I agree that the concat() needs the extra characters, but at that point I'd have expected it to work. I've done it before, even if only in examples, and I'm not seeing anything wrong with your syntax on brief glance.