Expression help, count the number of people whose substring count =1 or 2

[stevelord]

Hi, I put this together to count the total number of interactions for a group of people by client and it looks to be giving correct counts.  In the sub-tasks field, people have a CT-# notation added to their record on the same line for each interaction.

Data looks like:

Client Name        ExternalID   Sub- Tasks

AcmeAnvils              123               CT-123, CT-456

AcmeAnvils               456                CT-987

and my expression to count the interactions is:

SUM(substringcount(aggr([Sub-Tasks], ExternalID, [Client Name]), 'CT-'))

The answer it gives is 3 and it is correct.

Now I'm trying to make an expression that counts 'the number of people who had 1-2 interactions'.  In this case, the desired answer is 2.

I tried wrapping the above with count(if(...=1),ExternalID) + count(if(...=2),ExternalID) and the like, but am not getting to any correct answers.  (I actually have another column for people with 3-4 interactions and one for people with 5+, but they will be the same concept.)

Help, please.  Thanks!

-Steve

[maxgro · Accepted solution]

maybe

=sum(

if(aggr(substringcount([Sub- Tasks],  'CT-'), [Client Name], ExternalID)=1,1,

if(aggr(substringcount([Sub- Tasks],  'CT-'), [Client Name], ExternalID)=2,1)

))

[stevelord]

Actually, the desired result in the dummy data was 2.  The desired result in my actual data was 1.

Your answer was correct.

I also modified it for 3 and 4 contacts and >4 contacts for the other two columns, and got the desired results in those buckets.

Thanks!!

[simenkg]

You CAN do this in your application but it would be much easier to do it in the script.

just load subfield([Sub- Tasks],',') as [Sub- Tasks]

You can keep this in its own dimension and then add a field "number of subtasks" to you fact.

letft join(Facts)

load [Client Name], count(distinct [Sub-Tasks]) as [Sub-Tasks Count] resident SubTasks group by [Client Name]

Then you can eitehr use [Sub-Tasks Count] as a dimension in a chart with the expression Count(distinct [Client Name])

Or if you want to use a text box to display the number of clients with 2 sub-tasks you use:
Count({<{Sub-Tasks Count]={2}>} distinct [Client Name])

[stevelord]

Possibly easier, but the other solution is already done now which makes it quicker if I start now.

I do see how your idea would enable us to filter our selections based on who had how many interactions.  If end users begin requesting this, I will circle back with the script-based solution to create the field with the counts in it.  (Right now I think they're just trying to get out from under too many hours toiling through massive excel exports to count the things. As with anyone I setup a qlikview for. )

[simenkg]

Forgive me if you find this to be rude, but there is a quick way and there is a right way.

The solution marked as correct is complex, hard to maintain, not very flexible and VERY slow. Sum(If()) should never be used unless there is no alternative. If you have any amount of data then this will crawl to a halt.

The only short term benifit of the above solution is job security as you will certainly have to come back and change it.

[stevelord]

Apologies if I came off as wistful.  In reality this is for a single straight table evaluating a single spreadsheet on a monthly basis for a single worker doing a single task in temporary fashion until the task is handed off to a parent group that's building their own process.  Job security's not a concern because we tried to hand this one off earlier and were asked to hold another year.

A better distinction might be one between 'good' and 'optimal' and not wrong or right.  The first answer to meet my specific business need and solve my specific problem was correct.

I have done counting in script for large-scale solutions, and found myself having to revisit and break those processes down to smaller chunks due to infrastructure limits, so even a nice simple count() group by in script can get complicated if the dataset is very large and the infrastructure is a step behind.  Even so those were the best/only way to do the computations on the large scale.

[stevelord]

Actually, it’s two big spreadsheets but it was a crazy amount of work the worker was doing to manually count the stuff in excel, and still very small/quick by qlikview standards.

Steve Lord

Senior Reporting Analyst

direct: 240.482.8552

<http://www.livehealthier.com/>