Comparing dates in set analysis

[walkasia]

Data Model:

Activities Table contains ContactId, ActivityId, ActivityDate, ActivityDescription

Outputs Table contains ContactId, OutputDate, OutputDescription

I am trying to create a table that lists:

Contact ID, Output Date, number of Activities that precede that Output Date.

The set expression I have for the number of Activities that precede that Output Date is:

Count({<ActivityDate={"<=OutputDate"}>} ActivityID)

However, this returns nothing.  

Thanks in advance for any help!

[BrunPierre]

Try this

Count({$<ActivityDate={"=ActivityDate<=OutputDate"}>} ActivityID)

[Chanty4u]

Try this 

Count({<ActivityDate={"<=$(=OutputDate)"}>} ActivityID)

[marcus_sommer]

It's not possible with a set analysis else it will require if-loops and/or aggr() constructs. Therefore it would be better to do the essential work within the data-model by mapping one date to the other and calculating there directly the offset as a flag - which you could then query within a set analysis.

[agigliotti]

Hi @walkasia ,

I'm agree with @marcus_sommer  about how to manage this, however you could try the below expression:

Count( {< ContactId = {"=ActivityDate<=OutputDate"}>} ActivityID )

I hope this can helps.

Best Regards