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: 
Not applicable

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III


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.

View solution in original post

8 Replies
Miguel_Angel_Baeyens

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!

johnw
Champion III
Champion III

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

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

Not applicable
Author

Hi Miguel,

Thanks very much for your reply.

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



Not applicable
Author

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

Miguel_Angel_Baeyens

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.

johnw
Champion III
Champion III


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.

Not applicable
Author

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

bruno_m_santos
Partner - Creator
Partner - Creator

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