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.

[sunny_talwar]

If the above works, can you try one of these:

1)

=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')

2)

=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')

[#3]

Thanks.

Unfortunately, this seems to have no effect at all, just like the previous suggestion.

Also like the previous suggestion, the editor looks like it has a problem with the -=. It puts a little squiggly mark underneath it as if to say, "You can't do that here"

[sunny_talwar]

Even though you get that squiggle line underneath -=, it doesn't mean that your expression is incorrect. Still if you want to avoid it, try this:

=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 = e({<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')

[sasiparupudi1]

There is a comma missing between the values in the exclusion list

=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')

[sunny_talwar]

Good catch

=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 = e({<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')

[#7]

Is that little "e" supposed to be there?

ALL_USER_PERSON_TYPE = e({<ALL_USER_PERSON_TYPE...

I'm guessing that's something I haven't seen yet

[sunny_talwar]

Yup, that little e means to exclude those values .

[#9]

UPDATE:

OK, first of all, I have to apologize to everyone who was kind enough to take the time to respond.

For two reasons:

My boss was the one who was having this problem, and he asked me to post the question because he doesn't have an account here. So minor apologies for the fact that it wasn't my problem to begin with. I know that's not a big deal, though.

But I point that out because I don't want to look like an idiot, which, as you'll soon see, is a legitimate concern.

What IS, however, important, is that at least one of you almost certainly answered correctly, and at this point I'm not sure who.

I went in to my boss's office after the last suggestion and told him to copy and paste the answer. As he was doing so, I noticed a discrepancy between the title of the relevant table and the line in the expression he has been having so much trouble with all day.

It turns out he had been asking the wrong data field all along. As soon as I pointed it out and he changed it to the correct one, everything fell correctly into place.  It shouldn't have been ALL_USER_PERSON_TYPE to begin with. That field will never contain any of those values.

So my apology stems from the fact that I don't know who to credit for the correct answer.

If you have any suggestions, please let me know.

Thanks for everyone's time.

[sunny_talwar]

Hahahaha

I guess you can randomly choose one answer as correct and reward others by choosing there responses as Helpful answers .

Best,

Sunny