Identify the "inverse" of a list of dates

[#1]

Hello. I have a 2xN table of people and dates. I need to derive the "inverse" dates for each unique person code. I would prefer to do this in a chart expression rather than my load script.

My original data looks like this:

Person	Contacted
John	5/10/13
John	5/15/13
John	5/17/13
John	5/21/13
Susan	4/6/13
Susan	4/15/13
Bob	5/5/13
Bob	5/6/13
Mary	4/9/13
Mary	4/15/13

I need to find all the dates for John that do not appear in this list, and all the dates for Mary that do not appear, etc. I realize I will have to constrain the inverse set with a start and end date, because otherwise the inverse set is infinite.

For more context... the field [Person] appears in other tables. I'm trying to build an expression something like min([NOT contacted date]) for each Person.

I've tried {1-$} set analysis type operations, but I don't think that's the right tool for the job, since I'm not trying to exclude selections.

Help!

[Gysbert_Wassenaar]

See attached example.

[#3]

Gysbert,

Thank you.. this is excellent!. I took your demonstration and unpicked the code until I understand it. I now have a calendar by each person with a 1 or null flag by day. I even found a mapping load method to flip the nulls (representing not contacted days) to zeros. Google tells me this is bad, but it's helping clarify things while I play around.

I have been able to change your expression:

concat({1-<ContactedFlag={1}>}distinct Contacted, ' ')

and turn it into either:

MIN({1-<ContactedFlag={1}>} Contacted)

or:

MIN({<ContactedFlag={1}>}Contacted).

What I'm trying to do now is combine both of my expressions above. I want to find the lowest not contacted day after the lowest contacted day.

Conceptually, I want to do this (just directly nesting the two:

MIN({1-<ContactedFlag={1}>} >= MIN({<ContactedFlag={1}>}Contacted))

This is not playing nice. When you have time, please let me know what you think. You have been incredibly helpful so far!

Thanks!

[Josh_Good · Accepted solution]

Hi,

Please take a look at the attached and see if it does what you are looking for.  Notice if you select different dates then the result will change accordingly.

Regards.

[#5]

Josh,

That is perfect! Thank you.

In addition, I built on the framework you provided with a more complex expression sitting on top of a test variable:

min({<FirstDayNotContactedFlag={1}, Contacted={">=$(vTestInputDate)"}>} Contacted)

Thanks,

Stephen