Between Operator and Not Equal to

[msmahmood]

Need assistance regarding following condition how could I write it down in SET ANALYSIS identifier would be 1:

v_ass_period is a veriable

Rest of fields are from data set

Count(
if (v_ass_period >= ASSIGN_START_DATE AND v_ass_period <= ASSIGN_END_DATE

                  AND ASSIGNMENT_STATUS_TYPE_ID <> 3 ,EMPLOYEE_NUMBER,Null()

   )
)

[stevedark]

Hi @msmahmood 

What you are requiring is something like:

Count({<ASSIGN_START_DATE*={"<=$(v_ass_period)"}, ASSIGN_END_DATE*={">=$(v_ass_period)"}, ASSIGNMENT_STATUS_TYPE_ID-={3}>}EMPLOYEE_NUMBER)

Note that you will need to ensure that the variable v_ass_period returns the date in the exact same format as the ASSIGN_ date fields are in the source data. You should probably use the Date() function in the variable to ensure this.

You may, or may not, want to put the DISTINCT instruction inside the count (but outside squiggly brackets) to not double count employees.

Note that greater than and less than queries appear within the set with an equals before the set, whereas not equals has the minus sign before the equals sign. It does kind of make sense, but it is not obvious.

Hope that gets you what you need.

Steve

[msmahmood]

Hi Stevedark,

Many thanks for your reply yes i've already followed the recommended syntax to write down the expression but I don't find the required result.

Like:

Count({1<"$(v_ass_period)"={">=(ASSIGN_START_DATE) <=(ASSIGN_END_DATE)"}, ASSIGNMENT_STATUS_TYPE_ID-={'3'}>} ASSIGNMENT_ID)

Yes the Parameter has the same date format as of Assign_date.

Please further guide if the set analysis has any syntax error.

Regards,

MSMahmood

[stevedark · Accepted solution]

Hi @msmahmood 

Please look carefully at the differences between my statement and yours. The two table fields need to be listed separately and then the variable needs to be within the squiggly brackets.

The way to test where an issue resides is to build up your set analysis set by step. Checking the result at each point.

Start with:

count(ASSIGNMENT_ID)

Then bring in a single bit of set analysis:

count({<ASSIGNMENT_STATUS_TYPE_ID-={3}>} ASSIGNMENT_ID)

See if that works still. Note the removal of the quotes, as the type is a numeric (use quotes if it is a mixed type field).

Then try adding one date:

count({<ASSIGNMENT_STATUS_TYPE_ID-={3}, ASSIGN_START_DATE*={"<=$(v_ass_period)"}>} ASSIGNMENT_ID)

If that works, then try bringing in the other date and you have your final statement:

count({<ASSIGNMENT_STATUS_TYPE_ID-={3}, ASSIGN_START_DATE*={"<=$(v_ass_period)"}, ASSIGN_END_DATE*={">=$(v_ass_period)"}>} ASSIGNMENT_ID)

I note that you have flipped from using EMPLOYEE_NUMBER to ASSIGNMENT_ID - which is correc?

The 1 you have in your set analysis will ignore all selections - I doubt you want that to be there.

If adding in a date breaks the expression try replacing the variable with a typed date to see if you can make it work, e.g.

count({<ASSIGNMENT_STATUS_TYPE_ID-={3}, ASSIGN_START_DATE*={"<=01 Jan 2022"}, ASSIGN_END_DATE*={">=02 Jan 2022"}>} ASSIGNMENT_ID)

If that works, then the issue resides in the variable. The variable content should start with an equal symbol, and if you add it to a table with this expression:

='$(v_ass_period)'

It should return the date in the correct format.

Sometimes set analysis takes a bit of tweaking until it works, but if you follow step by step you should get there.

Steve

[msmahmood · Accepted solution]

Hi Steve,

It's wonderfully explained and it worked. I really appreciate, you solved my problem.

Thanks

MSMahmood

[stevedark]

That's great to hear - thanks for confirming and helping the community by marking as correct.

Steve