Conditional Textcount

richnorris — Tue, 21 Jul 2009 20:37:38 GMT

Hi there, I'm trying to do a chart of employees taking part in activities with clients. For some of the activities, such as 'training - one on one' I want to count all the instances of an employee taking part in that activity with a single client. Something like 'e-mail' I want to count only one instance of for each client, no matter how many times they have sent e-mail to that client.

To begin with, I tried to isolate all the activities that I want to only ever show one, and wrote a textcount distinct:

Textcount(DISTINCT{<Activity.TYPE={'Email','Telephone Call - Voiceblast', 'Telephone Call - Voicemail'}>}Activity.TYPE)

which sort of works, but now the chart shows all of those activities for the selected client regardless of any filters I try and apply? Is there a way round this? Also, if this is a bug, ignoring it, how would I then go about adding a Sum(Activity.TYPE) for everything else on the end of that?

Thanks!

Conditional Textcount

johnw — Wed, 22 Jul 2009 01:34:30 GMT

Maybe this?

count(distinct if( match(Activity.TYPE,'Email','Telephone Call - Voiceblast','Telephone Call - Voicemail'),Activity.TYPE))
+ count( if(not match(Activity.TYPE,'Email','Telephone Call - Voiceblast','Telephone Call - Voicemail'),Activity.TYPE))

Ignoring your filters isn't a bug, because set analysis is designed to be able to ignore filters. In this case, you've specifically told QlikView that you want those three activity types regardless of what activity types are selected. So in your case, you want to stick with something more basic, like an if(). The above expression can be simplified, but I wanted to make clear how similar the two halves were in concept, that the two halves together encompassed the whole set of selected data (match() vs. not match()), and that one was counting only one instance (distinct) and the other was not.

topic Conditional Textcount in QlikView

Conditional Textcount

Conditional Textcount