Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set expression that works correctly as follows:
=NUM(sum( {$<CurrentHRStatus={'Hired (*' },
CurrentReqStatus={0,5},
ReqFormTypeID_orig={29,384},
WorkType1-={'*Intern*'},
US_PositionStatus2-={'NO_POSITION'}>} Flag_HRStatus),'###')
However, when I add ActionDate <= WeekEnd the expression does not work? How can I get this to work for ActionDate <= WeekEnd ? Note that WeekEnd is a field and not a variable.
=NUM(sum( {$<CurrentHRStatus={'Hired (*' },
ActionDate={"< =WeekEnd"},
CurrentReqStatus={0,5},
ReqFormTypeID_orig={29,384},
WorkType1-={'*Intern*'},
US_PositionStatus2-={'NO_POSITION'}>} Flag_HRStatus),'###')
Please advise as soon as possible. Thank You!
Set analysis is sensitive to the data format. Try
ActionDate={"<=date(WeekEnd)"}
Hi Michael,
This doesn't work. If WeekEnd field is stored as an Integer then would this expression need to be changed? I've tried everything and nothing works?
Try adding $() around the variable name
=NUM(sum( {$<CurrentHRStatus={'Hired (*' },
ActionDate={"< =$(WeekEnd)"},
CurrentReqStatus={0,5},
ReqFormTypeID_orig={29,384},
WorkType1-={'*Intern*'},
US_PositionStatus2-={'NO_POSITION'}>} Flag_HRStatus),'###')
Hi Colin,
It turns out that ActionDate is an Integer format and WeekEnd field (not a variable) is a Date format. Do I need to update the script so that both are the same format so that it works in the Set Expression? Appreciate your assistance!
Try this in your set expression. $(= ) will evaluate the function that follows the equals sign.
Num will convert the date variable to the integer value
Do not add any spaces between the 3 characters $(= or the expression will fail.
ActionDate={"<=$(= num($(WeekEnd)) )"},
Oops, if WeekEnd is a field then use
ActionDate={"<=$(= num(WeekEnd) )"},
Hi Colin,
This still doesn't work. I think the revised set expression ActionDate={"<=$(= num(WeekEnd) )"},
is correct though. Could it be something incorrect in my data model? Should I send you a file to look at? I'm baffled!
Hi Colin,
Thanks for the info. I got the set expression to work with another variable field. I appreciate your help.
Below is the correct set expression.
=NUM(sum( {$<CurrentHRStatus={'Hired (*' },
$WEEK_ID={"<=$(=varOpeningsReportWeekID)"},
CurrentReqStatus={0,5},
ReqFormTypeID_orig={29,384},
WorkType1-={'*Intern*'},
US_PositionStatus2-={'NO_POSITION'}>} Flag_HRStatus),'###')
Hi Robyn,
if you got the answer then mark the answer right , such that other users can use this for reference in future.