Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

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.

8 Replies
MVP
MVP

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!

MVP
MVP

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])

Not applicable

Set Analysis and concat

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

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

MVP
MVP

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.

MVP
MVP

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.

Not applicable

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

bruno_m_santos
Contributor

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

Community Browser