Concat() - how to use the list in Set Analysis

[#1]

Hi All

I've created a simple example, which I just cannot get to work.

sum ({<SalesPerson={'AA','BB','EE'}>}Sales)

I have the above formula and would like to replace the 'AA','BB','EE' string using the Concat() formula.

I tried the following, but cant get it to work, the portion in green is intended to replace the 'AA','BB','EE' as noted above.

=sum({<SalesPerson={"=concat({<Include = {'Y'}>}chr(39) & SalesPerson & chr(39),',')"}>}Sales)

Thank you!!

LOAD

* INLINE [

    SalesPerson, Include, Sales

    AA,      Y, 1

    BB,      Y, 1

    CC,      N, 1

    DD,      N, 1

    EE,      Y, 1

];

[swuehl · Accepted solution]

I think you need to use a dollar sign expansion in the set modifier:

=sum({<SalesPerson={$(=concat({<Include = {'Y'}>}chr(39) & SalesPerson & chr(39),',') )}>}Sales)

and I would also maybe add a DISTINCT qualifier to the concat.

[#3]

Great, it works!!

Thanks

[#4]

Out of interest, where would I place the Distinct qualifier?

[#5]

ok, figured it out....

= concat( DISTINCT{<Include = {'Y'}>}chr(39) & SalesPerson & chr(39),',')

[phongnnguyen]

Hi Stefan,

Do you have any idea about this analysis without SUM

sum({<SalesPerson={$(=concat({<Include = {'Y'}>}chr(39) & SalesPerson & chr(39),',') )}>}Sales)

I just want show the list value of Holiday by concat

Actually, My set analysis like this:  firstworkdate(tradedate,7,concat({<checkwd= {'1'}>}chr(39) & tradedate & chr(39),','))

With checkwd is Holiday field, normal day is 0, Holiday is 1

Thanks for your help