Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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!
I think you should be able to use another field as an element set:
sum({1<[Ordering Depot]=Depot>} [Number Orders])
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
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
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.
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.
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
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