
How to count excluded/original data from within an expression
Roland Kunle Nov 16, 2010 11:06 AM (in response to David Rowe)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 "exceptionbrackets" 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
David Rowe Nov 16, 2010 11:50 AM (in response to Roland Kunle )Thanks Roland,
This gets me a count of Case against Analyst against nonnull Solution_Author.
I have also tried
count({$1<CASE=>} Solution_Author)
Which gives me a Count against all Analyst against nonnull 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.
DR

How to count excluded/original data from within an expression
John Witherspoon Nov 16, 2010 7:51 PM (in response to David Rowe)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, EdIf I select Product_Team B, that gives us cases 4 and 5. You want to exclude those cases and look at only cases 13. 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.

testCountExcluded.qvw 121.8 K

How to count excluded/original data from within an expression
David Rowe Nov 17, 2010 9:51 AM (in response to John Witherspoon )Attached a new tab.
Normal 0 false false false ENGB XNONE XNONE 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, CarlaLooking 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.

testCountExcludedDR.qvw 137.2 K

How to count excluded/original data from within an expression
John Witherspoon Nov 17, 2010 12:32 PM (in response to David Rowe)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
David Rowe Nov 17, 2010 12:56 PM (in response to John Witherspoon )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
DR

How to count excluded/original data from within an expression
John Witherspoon Nov 17, 2010 2:54 PM (in response to David Rowe)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.

testRoles.qvw 122.8 K

How to count excluded/original data from within an expression
David Rowe Nov 17, 2010 3:07 PM (in response to John Witherspoon )Thanks John, works a treat. Taught me how to work with crosstable too.






