Intersect between two 'sets' without Set Analysis

[Zed_25]

I have a sheet where the user can select two dates: Start Date and End Date. These are stored in variables.

I have a table where I list the IDs of all records that were active on Start Date, and another table that lists the IDs that were active on End Date. These tables are populated using:

For table 1:

=IF(STATUS = 'Open'
and [DATA_EXTRACT_DATE] = '$(vStartDate)'
, ID)

For table 2:

=IF(STATUS = 'Open'
and [DATA_EXTRACT_DATE] = '$(vEndDate)'
, ID)

I know that I could use Set Analysis to count the number of IDs which were open on both dates (i.e. the intersect of both sets) but I would like to list the IDs rather than perform aggregation.

Is there a way of doing this by combining the IF statements? Please can anyone advise.

Many Thanks

[JordyWegman]

Hi Zed,

You can use alternate states for this if you don't want to use set analysis. With if statements, the associative engine will make the associations when you are selecting things in your dashboard.

Jordy

Climber

[sunny_talwar]

Not sure, but may be this

=If(STATUS = 'Open' and ([DATA_EXTRACT_DATE] = '$(vStartDate)' or [DATA_EXTRACT_DATE] = '$(vEndDate)'), ID)