topic Re: Comparing 2 date fields set analysis in QlikView

Comparing 2 date fields set analysis

zagzebski — Mon, 18 May 2015 14:21:10 GMT

I have tried to find an example for what seems like a basic set analysis expression - but can't find one that matches what I need. Just trying to return Revenue where the

Employee Last Date is <= StartTime_Date

but obviously my syntax is wrong. some of the examples I tried:

Sum ({<[Employee Last Date]={'<=$(=Date(([StartTime_Date])))'>}Revenue)

Sum({<[Employee Last Date]={'<=$(Date([StartTime_Date]))'>}Revenue)

Sum({<[Employee Last Date]={'<=[StartTime_Date]'>}Revenue)

Steve

Re: Comparing 2 date fields set analysis

Gysbert_Wassenaar — Mon, 18 May 2015 14:32:38 GMT

You might be running into another problem: set_analysis_intra-record.qvw

If both date fields are part of the same data table you create in the load script then you should add a flag field in this table:

LOAD

EmployeeID,

[Employee Last Date],

StartTime_Date,

if([Employee Last Date] <= StartTime_Date, 1, 0) As FlagField,

...other fields...

FROM ....

You can then use this field in your set analysis expression instead: sum({<FlagField={1}>}Revenue)

Re: Comparing 2 date fields set analysis

pokassov — Mon, 18 May 2015 14:33:06 GMT

Hello!

Sum ({<[Employee Last Date]={'[Employee Last Date]<=[StartTime_Date]'>}Revenue)

Re: Comparing 2 date fields set analysis

zagzebski — Mon, 18 May 2015 15:21:05 GMT

Thanks. Think Gyspert caught my issue.

Re: Comparing 2 date fields set analysis

zagzebski — Mon, 18 May 2015 15:29:05 GMT

Very helpful document. I all the Set analysis trainings I have experienced nobody went through this. Thanks!

Re: Comparing 2 date fields set analysis

zagzebski — Mon, 18 May 2015 15:34:03 GMT

I do have one question. In your example would everythign still apply if you took out (1) OrderDate, (2) Shipdate or (3) both OrderDate and ShipDate from your table. Would that change how your set would work?