Help with Concatenated value variable in Set Expression

[jcs_2015]

I've created a variable based on a set expression:

vCohortMbr_test =

Concat(
{<
Eligibility = {'E'},
MEMBER_ID = {'*'},
[Report Date] = {"$(='>=' & Date($(vCE),'YYYY-MM')) $(='<=' & Date($(vCE),'YYYY-MM'))"},
[Assessment Date] = {">=$(=$(vCS)) <=$(=$(vCE))"}
>}
DISTINCT Chr(34) & MEMBER_ID & Chr(34),',')

that generates a string of Member IDs that

1) Have a status of Eligible

2) MEMBER_ID must not be Null

3) In a set reporting period (single month, last month of the cohort period based on an island calendar picklist) [Report Date]

4) That also had a date value [Assessment Date] within the cohort reporting window which is based on vCS and vCE for the start/end date values for selected cohort timeframe.

Based on that unique Member ID list of values I need to perfume some set expression, ie Count() and Sum()

However, I can't seem to get the variable to work in a set expression, simple syntax error I assume?

A sample Set expression I've tried looks like the below, having tested different iterations of single and double quotes and $( expansions without any luck.

sum(
{<
MEMBER_ID = {'$(vCohortMbr_test)'}
>}
CNT)

What am I doing wrong?

Thks

Jim

[sunny_talwar]

Try this (Remove the single quotes around your variable):

Sum({<MEMBER_ID = {$(vCohortMbr_test)}>} CNT)

[sunny_talwar]

Or this:

Sum({<MEMBER_ID = {$(=vCohortMbr_test)}>} CNT)

[jcs_2015]

Unfortunately that did not work Sunny, I've attached the model.  Yellow table is were I'm testing set expressions based on that variable.

[sunny_talwar · Accepted solution]

This worked

Sum({<MEMBER_ID = {$(=$(vCohortMbr_test))}>} CNT)

[jcs_2015]

Certainly did, Thanks!

Is there some sort of resource I can reference that helps with these types of syntax challenges for set expressions I can leverage for future needs?  Seems as if there are so many combinations of $(=... etc

When to use which would be and incredibly helpful resource.

[sunny_talwar]

You can try to read through this:

The Magic of Dollar Expansions

It this particular case I checked what worked in text box object

1) =vCohortMbr_test -> This did not work, showed me your expression

2) =$(vCohortMbr_test) -> Showed me all the individual values that your expression evaluated to....

Once I knew this works, I put that within the dollar sign expansion

$(    =$(vCohortMbr_test)      )

Call it my Text Box Test

[jcs_2015]

Thanks again, I'll be sure to give @ HIC's document a deep read.

[sunny_talwar]

You can also do this

Variable definition (with a equal sign)

=Concat(

{<

Eligibility = {'E'},

MEMBER_ID = {'*'},

[Report Date] = {"$(='>=' & Date($(vCE),'YYYY-MM'))  $(='<=' & Date($(vCE),'YYYY-MM'))"},

[Assessment Date] = {">=$(=$(vCS)) <=$(=$(vCE))"}

>}

DISTINCT Chr(34) & MEMBER_ID & Chr(34),',')

and then this

Sum({<MEMBER_ID = {$(=vCohortMbr_test)}>} CNT)

Read about the equal sign here:

The Little Equals Sign