Comparing 2 date fields set analysis

[zagzebski]

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

[Gysbert_Wassenaar · Accepted solution]

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)

[pokassov]

Hello!

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

[zagzebski]

Thanks. Think Gyspert caught my issue.

[zagzebski]

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

[zagzebski]

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?