Dollar-Sign Expansion's Arcane Syntax...

[#1]

... continues to give me fits.

I'm new at this. Be gentle.

Here's the problem.

This expression, to count the number of employees who meet a certain criteria, seems to work just fine:

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},
              ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},
              ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},
              ALL_ASG_END_DATE={">$(=vAsOfDate)"},
              ALL_JOB_TITLE={"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>}
  ALL_EMPLOYEE_NUMBER),'#,##0')

HOWEVER...

When I try to insert this line, containing a variable to exclude certain other types (after the END_DATE line above), it incorrectly returns zero:

ALL_USER_PERSON_TYPE={"-=$(=vUserStatusExceptions)"},

vUserStatusExceptions is defined thusly:

SET vUserStatusExceptions = 'SEPARATED' 'INACTIVE' 'AC TIVE APPLICATION' 'ACCEPTED' 'ACTIVE ASSIGNMENT' 'NEW HIRE';

So what is it this time that I'm doing wrong?

Thanks in advance.

[pokassov · Accepted solution]

Hi!

Try this

ALL_USER_PERSON_TYPE-={$(vUserStatusExceptions)}

SET vUserStatusExceptions = 'SEPARATED', 'INACTIVE','AC TIVE APPLICATION','ACCEPTED','ACTIVE ASSIGNMENT','NEW HIRE';

[sinanozdemir]

Hi,

Can you try this?

ALL_USER_PERSON_TYPE -={"$(vUserStatusExceptions)"}

Thanks

[#4]

Thanks. Unfortunately, that returned an ERROR

[jonathandienst]

Sergey's expression looks OK to me. Did you confirm that vUserStatusExceptions contains what you expect?

[sasiparupudi1]

Please try

SET vUserStatusExceptions = 'SEPARATED', 'INACTIVE','AC TIVE APPLICATION','ACCEPTED','ACTIVE ASSIGNMENT','NEW HIRE';

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

              ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

              ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

              ALL_ASG_END_DATE={">$(=vAsOfDate)"},

              ALL_USER_PERSON_TYPE-={"$(=vUserStatusExceptions)"},

              ALL_JOB_TITLE={"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>}

  ALL_EMPLOYEE_NUMBER),'#,##0')

hth

Sasi

[sasiparupudi1]

Please note that the code editor might show error line because on the - sign but the expression should be ok

[#8]

Thanks. This answer also refused to work correctly. The results didn't change at all from when the line was excluded altogether.

[#9]

Thanks.

Unfortunately, this seems to have no effect at all, like QV is ignoring the line altogether.

The editor also looks like it has a problem with the -=. It puts a little mark underneath it as if to say, "I don't know what this is"

[sunny_talwar]

What happen when you actually add the value instead of using the variable?

=Num(Count({<ALL_EFFECTIVE_START_DATE={"<=$(=vAsOfDate)"},

              ALL_EFFECTIVE_END_DATE={">$(=vAsOfDate)"},

              ALL_ASG_START_DATE={"<=$(=vAsOfDate)"},

              ALL_ASG_END_DATE={">$(=vAsOfDate)"},

              ALL_USER_PERSON_TYPE-={'SEPARATED' 'INACTIVE' 'AC TIVE APPLICATION' 'ACCEPTED' 'ACTIVE ASSIGNMENT' 'NEW HIRE'},ALL_JOB_TITLE {"$(=IF($(vOnlyTeachers)=1,'TEACHER','*'))"}>} ALL_EMPLOYEE_NUMBER),'#,##0')