topic Set Expression Date Comparisons in QlikView

Set Expression Date Comparisons

Anonymous — Tue, 13 Jan 2015 20:08:40 GMT

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!

Re: Set Expression Date Comparisons

Anonymous — Tue, 13 Jan 2015 20:13:10 GMT

Set analysis is sensitive to the data format. Try

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

Re: Set Expression Date Comparisons

Anonymous — Tue, 13 Jan 2015 22:46:17 GMT

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?

Re: Set Expression Date Comparisons

Colin-Albert — Tue, 13 Jan 2015 22:50:12 GMT

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

Re: Set Expression Date Comparisons

Wed, 14 Jan 2015 16:42:53 GMT

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!

Re: Set Expression Date Comparisons

Colin-Albert — Wed, 14 Jan 2015 17:14:35 GMT

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

Re: Set Expression Date Comparisons

Colin-Albert — Wed, 14 Jan 2015 17:22:03 GMT

Oops, if WeekEnd is a field then use

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

Re: Set Expression Date Comparisons

Wed, 14 Jan 2015 18:52:08 GMT

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!

Re: Set Expression Date Comparisons

Wed, 21 Jan 2015 17:26:45 GMT

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

Re: Set Expression Date Comparisons

ashwanin — Wed, 21 Jan 2015 18:50:52 GMT

Hi Robyn,

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