topic Re: Set analysis - show account names with missing data in last 30 days in QlikView

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

Thu, 20 Mar 2014 14:42:38 GMT

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.)

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

Nicole-Smith — Thu, 20 Mar 2014 14:50:24 GMT

In a text box:

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

Example file also attached.

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

Fri, 21 Mar 2014 14:29:30 GMT

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

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

Nicole-Smith — Fri, 21 Mar 2014 14:38:53 GMT

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, '

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

Fri, 21 Mar 2014 14:50:18 GMT

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.

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

Nicole-Smith — Fri, 21 Mar 2014 14:51:59 GMT

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, '

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

Fri, 21 Mar 2014 15:11:50 GMT

you're a life saver Nicole. Thanks