topic Re: Intersect between two 'sets' without Set Analysis in App Development

Intersect between two 'sets' without Set Analysis

Zed_25 — Tue, 25 Jun 2019 15:31:49 GMT

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

Re: Intersect between two 'sets' without Set Analysis

JordyWegman — Wed, 26 Jun 2019 09:26:31 GMT

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

Re: Intersect between two 'sets' without Set Analysis

sunny_talwar — Wed, 26 Jun 2019 12:03:07 GMT

Not sure, but may be this

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