topic Re: Re: Re: Exception query in QlikView

Exception query

Fri, 28 Feb 2014 17:19:54 GMT

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?

Re: Exception query

Fri, 28 Feb 2014 17:33:10 GMT

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

Re: Exception query

Fri, 28 Feb 2014 17:36:59 GMT

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

Re: Exception query

Mon, 03 Mar 2014 11:55:44 GMT

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)

Re: Exception query

Mon, 03 Mar 2014 11:57:59 GMT

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

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

How would this fit into the expression?

Re: Exception query

Mon, 03 Mar 2014 12:48:36 GMT

How would I extend this for the last 30 days?

Re: Re: Exception query

Mon, 03 Mar 2014 17:43:37 GMT

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

Re: Re: Re: Exception query

Mon, 10 Mar 2014 12:54:13 GMT

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

Re: Re: Re: Re: Exception query

Mon, 10 Mar 2014 15:23:08 GMT

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

Re: Re: Re: Re: Exception query

Mon, 10 Mar 2014 16:00:08 GMT

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)