topic How to count excluded/original data from within an expression in QlikView

How to count excluded/original data from within an expression

Tue, 16 Nov 2010 15:29:52 GMT

Apologies for a basic question. I have looked all over the site but can't find what I'm looking for.

I have data Case, Product_Team, Analyst, Solution_Author

I use a list box to select Product_Team which gives me a list of Analyst and I can do Case counts and Solution counts when owned/filtered by the Analyst on the Product_Team no problem. Solutions however can be used by other Product_Teams which is where I have my problem

What I am looking for is a way to count the Solution_Author where the Case is NOT filtered by the Product_Team ie from the Original data.

Appreciate any assistance.

David

How to count excluded/original data from within an expression

Tue, 16 Nov 2010 16:06:05 GMT

Hi,

for your expression you can use Set analysis similar to this:

count( { $ < Case= > } Solution_Author)

where $ means all user - selection are included. But the <> are the "exception-brackets" and in my expression any selection of Case isn't respected for this expression.

HTH, Roland

How to count excluded/original data from within an expression

Tue, 16 Nov 2010 16:50:24 GMT

Thanks Roland,

This gets me a count of Case against Analyst against non-null Solution_Author.

I have also tried

count({$1<CASE=>} Solution_Author)

Which gives me a Count against all Analyst against non-null Solution_Author.

Note Analyst and Solution_Author could be the same value.

I'm looking for an unfiltered count of case count against particular Analyst/Solution_Author.

The below counts the number of Cases where the 'filtered' Analyst = Solution_Author

Count (IF(Solution_Author=Analyst,Solution_Author))

Count (CASE) gives me a count of case against 'filtered' Analyst if that helps.

How to count excluded/original data from within an expression

johnw — Wed, 17 Nov 2010 00:51:35 GMT

I don't know if I'm following. Let's say you have this data:

Case, Product_Team, Analyst, Solution_Author
1, A, Bob, Carla
2, A, Carla, Dan
3, A, Bob, Dan
4, A, Bob, Bob
4, B, Ann, Ed
5, B, Bob, Ed

If I select Product_Team B, that gives us cases 4 and 5. You want to exclude those cases and look at only cases 1-3. For those cases, the Solution_Authors are Carla, Dan and Dan. So you want a count of 2 distinct Solution_Authors. Is that correct?

If so, it appears that this does the trick:

count({<Product_Team=,Case=E()>} distinct Solution_Author)

See attached.

How to count excluded/original data from within an expression

Wed, 17 Nov 2010 14:51:45 GMT

Attached a new tab.

Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4

Using this data, your data slightly modified (Carla). Changed Bob to Fred on Team B to avoid confusion.

1, A, Bob, Carla
2, A, Carla, Carla
3, A, Bob, Dan
4, A, Bob, Bob
5, B, Ann, Ed
6, B, Fred, Carla

Looking at Analyst/Solution_Author Carla. We see that when we filtered to her we show she is the Analyst on 1 case, authored the solution for 2 for the team and authored the solution for 3 cases in total. It is total I am having trouble getting to ie. the case with Carla's solution that Fred on Team B used.

How to count excluded/original data from within an expression

johnw — Wed, 17 Nov 2010 17:32:16 GMT

If we select Solution_Author Carla, a simple count(Solution_Author) gives you 3. I'm guessing that what you want isn't that simple, because you then say something I make no sense of at all, "the case with Carla's solution that Fred on Team B used". Yes, I can see a row of data for that. But what does that have to do with the total of 3 cases, other than the fact that it is one case being counted because Carla is the Solution_Author for it? I assume there's some point being made that I'm not understanding? Also, I'm having no luck trying to figure out how this relates to what seems like a completely different question in the original post, where you also say "where the case is NOT filtered by the Product_Team". Or are these two completely different questions? Also, the data in the file you posted doesn't match the data in the post itself (same people, different case numbers), so then I'm not certain when you give examples of counts whether you're refering to the file's data set or the data set in the post.

How to count excluded/original data from within an expression

Wed, 17 Nov 2010 17:56:34 GMT

Apologies for any confusion, hope this explains.

If you look at the file qvw I attached in the previous post. You will see in the Copy of Main (properties, expressions) that I have tried to add another column to the chart that gives a Total of solutions authored by the 'Team/Analyst', Carla in this case. Once the Team/Analyst has been selected I can't get to data outside of the filter.ie I can't count the solution of Carlas that Fred used.

Thanks

How to count excluded/original data from within an expression

johnw — Wed, 17 Nov 2010 19:54:12 GMT

So you've selected product team A. That team has analysts Bob and Carla. You're happy with the first three expressions for them. You're trying to add a fourth expression. You want a value of 3 for Carla for this fourth expression, because Carla has authored three solutions, and you don't care that one of those solutions is for team B, even though team A is selected? And you want 3 assigned to Carla, even though she was only the analyst for 1 of them, and the dimension is analyst? We might call this new expression "solutions provided by this analyst to ALL product teams and analysts"?

So far I've been unable to figure it out using only an expression. But the attached accomplishes what I think you want by adding a Roles table to the data model.

How to count excluded/original data from within an expression

Wed, 17 Nov 2010 20:07:05 GMT

Thanks John, works a treat. Taught me how to work with crosstable too.

Re: How to count excluded/original data from within an expression

abhi1693r — Wed, 17 Jul 2019 19:09:28 GMT

Hi John,

I am facing a similar problem.

I am counting 2 columns A and B, Count( DISTINCT A&'|'&B)

I want to exclude any cells with the word "ROM".

How can I do this ?