Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.)
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, '
')
In a text box:
=concat({<AccountTags.Name={'Tier 1'},Accounts.Name={'=max(Activity.CalendarDate)<today()-30'}>}distinct Accounts.Name, ', ')
Example file also attached.
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
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, '
')
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.
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, '
')
you're a life saver Nicole. Thanks