Set analysis - show account names with missing data in last 30 days

[#1]

I need to do a set analysis, but it's proving a bit tricky. I'm trying to return the Accounts.Name tagged as 'tier 1', which do not have an associated Activities.AccountID in the last 30 days.

My sample data has 4 columns and consolidates the underlying tables onto one.

Activity.CalendarDate

Activiities. AccountID

AccountTags.Name

Accounts.Name

In my example data I have 5 accounts and 2 of them are tagged as  'tier 1'. However, of the two 'tier 1' firms only Big Company llp has not had an activity within the last 30 days, so that is the result I would expect.

The underlying relationships of the tables is as follows (for the purpose of this question, the Business and Contacts tables can be ignored.)

[Nicole-Smith]

In a text box:

=concat({<AccountTags.Name={'Tier 1'},Accounts.Name={'=max(Activity.CalendarDate)<today()-30'}>}distinct Accounts.Name, ', ')

Example file also attached.

[#3]

Hi Nicole,

this seems to be correct, which is great.

However, how can i make it immune it from any selections? The 'detach' option deosn't seem to be available for text boxes.

Secondly, would it be possible to insert a line break after each result instead of a comma?

thanks

alex

[Nicole-Smith]

To make it immune to selections, you need a 1 in the set analysis.  To have a line break, the following seems to work although I don't know if it will persist or not once it's on the server:

=concat({1<AccountTags.Name={'Tier 1'},Accounts.Name={'=max(Activity.CalendarDate)<today()-30'}>}distinct Accounts.Name, '

')

[#5]

ha, it's as simple as hitting Enter, i was trying to put script in like <br>

oddly adding the 1 doesn't seem to make any difference, as when i start selecting different date ranges to filter other data on the sheet, it still impacts the text box.

[Nicole-Smith · Accepted solution]

Oh, I missed the other one that's needed in the set analysis within the set analysis:

=concat({1<AccountTags.Name={'Tier 1'},Accounts.Name={'=max({1}Activity.CalendarDate)<today()-30'}>}distinct Accounts.Name, '

')

[#7]

you're a life saver Nicole. Thanks