Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this (Remove the single quotes around your variable):
Sum({<MEMBER_ID = {$(vCohortMbr_test)}>} CNT)
Or this:
Sum({<MEMBER_ID = {$(=vCohortMbr_test)}>} CNT)
Unfortunately that did not work Sunny, I've attached the model. Yellow table is were I'm testing set expressions based on that variable.
This worked
Sum({<MEMBER_ID = {$(=$(vCohortMbr_test))}>} CNT)
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.
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
Thanks again, I'll be sure to give @ HIC's document a deep read.
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: