topic Re: Help with Concatenated value variable in Set Expression in QlikView

Help with Concatenated value variable in Set Expression

jcs_2015 — Tue, 10 Jan 2017 16:43:50 GMT

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

Re: Help with Concatenated value variable in Set Expression

sunny_talwar — Tue, 10 Jan 2017 16:48:13 GMT

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

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

Re: Help with Concatenated value variable in Set Expression

sunny_talwar — Tue, 10 Jan 2017 16:48:56 GMT

Or this:

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

Re: Help with Concatenated value variable in Set Expression

jcs_2015 — Tue, 10 Jan 2017 16:58:36 GMT

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

Re: Help with Concatenated value variable in Set Expression

sunny_talwar — Tue, 10 Jan 2017 17:01:24 GMT

This worked

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

Re: Help with Concatenated value variable in Set Expression

jcs_2015 — Tue, 10 Jan 2017 17:05:55 GMT

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.

Re: Help with Concatenated value variable in Set Expression

sunny_talwar — Tue, 10 Jan 2017 17:10:56 GMT

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

Re: Help with Concatenated value variable in Set Expression

jcs_2015 — Tue, 10 Jan 2017 17:13:25 GMT

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

Re: Help with Concatenated value variable in Set Expression

sunny_talwar — Tue, 10 Jan 2017 17:13:41 GMT

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