8 Replies Latest reply: Nov 17, 2010 3:07 PM by David Rowe RSS

    How to count excluded/original data from within an expression

      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

          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

              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.

              DR

               

               

               

               

                • How to count excluded/original data from within an expression
                  John Witherspoon

                  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

                      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
                          John Witherspoon

                          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

                              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