Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Set Expression Date Comparisons

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!

9 Replies
Highlighted
Champion III
Champion III

Set analysis is sensitive to the data format.  Try

ActionDate={"<=date(WeekEnd)"}

Highlighted
Contributor
Contributor

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?

Highlighted

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),'###')

Highlighted
Not applicable

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!

Highlighted

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)) )"},

Highlighted

Oops, if WeekEnd is a field then use

     ActionDate={"<=$(= num(WeekEnd) )"},

Highlighted
Not applicable

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!

Highlighted
Not applicable

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),'###')

Highlighted
Specialist
Specialist

Hi Robyn,

if you got the answer then mark the answer right , such that other users can use this for reference in future.