Exception query

[#1]

I have the following tables linked

For this question, please ignore the Role and Businesses table

As you can see each activity entered by one of our salespeople is linked to a contact, which is linked to an account

I need to produce a straight table which shows me Accounts which have had no activities in the last month. How would i do this?

[#2]

Hi Alex

I'm sure there is a better solution, but this is the first which came to my mind:

Dimension: Accounts

Expression: If(COUNT(ActivityContactID) = 0, 1, 0)

Presentation => Supress Zero Values checked

Lukasz

Edit: you can extend with set analysis to take only the last month into consideration

[#3]

From your data model all the tables seem to be nicely assoicated. So on the front-end put acountsID and Activity.CalendarDate in a table box. Where ever there is a null() against Account ID then those don't have any activities.

Now in a straight table

Add AccountID as dimension

Expression something like: if(isnull(Acitivity.LastMonth),1,0)

Activity.lastMonth is a flag on back-end to find out the last month.

Hope it helps

Thanks

AJ

[#4]

Hi Ajay, this is very close but not quite right.

Last Month only literally gives me the last calendar month. It's my fault as I should have been clearer in my question, but I actually meant the last 30 days.

how would the expression differ, would it be something like this

if(isnull(Activity.DaysAgo<=30),1,0)

[#5]

I also need to apply to following set analysis to the table.

{$<AccountTags.Name={'tier 1'}>}

How would this fit into the expression?

[#6]

How would I extend this for the last 30 days?

[#7]

Is it possible to attach a sample doc with data source files as well.

If you want a general suggestion your data your data might look like this in a table box:

Account.AccountID	Accounts.CreatedDate	Activity.CalendarDate
23	3/2/2014	3/3/2014
45	1/1/2014	1/3/2014
345	12/30/2013	1/1/2014
213	11/2/2013	12/4/2013
345	9/2/2013	10/16/2013
2342	5/1/2013	5/1/2013
092	2/27/2014
234	1/2/2013
1322	3/1/2014

Now from this you can find out that the last three don't have a Activity date which means those IDs dont have any Activities but those would have Accts.CreatedDate.

In this case you want 092 and 1322 only as they are within last 30 days right?

So you can create a flag for last 30 days based on Accts.CreatedDate  as 1/0 and left join Activity.CalendarDate to Accounts table and create field like if(isnull(Activity.CalendarDate),1,0) on backend.

Now on front end your expression could be

Only({<Accounts.CreatedDateFlag={1},Activity.CalendarDateFlag={1},AccountTags.Name={'tier 1'}>}Account.AccountID)

Thanks

AJ

[#8]

Hi Ajay,

i've attached some data.

Essentially i need to return the Accounts.Name where AccountTags.Name is 'tier 1' and where there are no Activities.AccountID values for each Accounts.Name for the last 30 days

[#9]

Hey Alex,

The data you provided doesn't seem to have null Activities.AccountID for AccountTags.Name= 'tier 1' in the last 30 days.

So added two rows to the excel with the criteria you mentioned.I created a field called ReqField on the back-end which will give you values you are looking for.

Thanks

AJ

[#10 · Accepted solution]

I've worked it out now, with a lot of help. This was the solution

If(count({$ <Activity.CalendarDate={'>=$(=Date(Today()-30))<=$(=Date(Today()))'}, AccountTags.Name={'Tier 1'}>} Activities.AccountId)=0, 1,0)