15 Replies Latest reply: Jan 15, 2014 4:28 AM by Pooja Sharma RSS

    *** Sports Analysis (And-Selection) Mode ***

    Deepak Vadithala

      Dear All,

       

      I need help with And-Selection mode. I have attached the excel file which contains raw data. I have types of games and I wanted to understand my audience. I need to use And-Selection mode.

       

      I,e. I want to know how many of them went to Football and Rugby (both games not just one of them). QV’s original behaviour is to show the OR selection mode. And I wanted to see the combination of audience who went to both the games.

       

      Here is the further information...

       

      Dimension : Gender

      Expression : Count(Unique_ID)

       

      I hope this is clear. Please let me know if you have further questions.

       

      Also, please let me know if someone have interesting ideas to visualize this kind of data.

       

      Thanks in advance.

       

      Cheers - DV

        • Re: *** Sports Analysis (And-Selection) Mode ***
          Stefan Wühl

          Hi D V,

           

          I used a second table for the AND mode according to the AND mode requirements in the HELP. Please see attached app.

           

          For the visualiation, maybe a pie chart (just two values in dimension), but I prefer bar charts.

           

          Regards,

          Stefan

            • Re: *** Sports Analysis (And-Selection) Mode ***
              Deepak Vadithala

              Hi Stefan,

               

              Hope you are doing well. Thanks for quick response and I really appreciate your help.

               

              I have done the same yesterday and I am getting inconsistent results. For example, let's take Cricket and Football as sports. Below are the breakdown from your QV file :

               

              Incorrect Result

              Female : 7

              Male : 10

               

              Which does not seem to be right... If I do a quick VLookup (In Excel) on the Unique_ID between Cricket and Football games. I get the below breakdown :

               

              Correct Result

              Female : 3

              Male : 4

               

              Because we only wanted audience who went to both the matches and not one of them. Any thoughts?

               

              Thanks again for your help.

               

              Cheers - DV

                • Re: *** Sports Analysis (And-Selection) Mode ***
                  Stefan Wühl

                  Maybe just using a

                  =count(distinct unique_id)

                   

                  then?

                   

                  P.S: Just to clarify, I mean replacing the chart expression count(Unique_ID) in my above sample app with a distinct count, of course.

                  This will result in the count of distinct visitors, not counting all their visited games (including the implicitely selected ones).

                  I think that's what you are after.

                   

                   

                    • Re: *** Sports Analysis (And-Selection) Mode ***
                      Deepak Vadithala

                      Stefan - Thank you very much. This is the right direction. You are right, using the Distinct clause within the expression returns the unique count for both Football & Cricket excluding all other games. However, if I don't use the Distinct clause then I am counting the same audience across all other games. Is this right? I really wanted to understand the implication of using and not using the Distinct clause.

                       

                      Kaushik - I can't get your solution to work. Can you workout an example please?

                       

                      Stefan / Kaushik - Personally, I don't want to use the And-mode method to get this working. So I really appreciate if you guys let me know any other approach to get this solution. If there is no work around then I would stick using the And-mode (above solution).

                       

                      I hope this make sense. I'll look forward to hear from you.

                       

                      Thanks,

                      DV

                        • Re: *** Sports Analysis (And-Selection) Mode ***
                          Goran Korsgren

                          Hi DV

                           

                          This illustration shows what happens if you count(Unique_ID) vs count(Distinct Unique_ID)

                           

                          ditinctand.JPG

                          I have used and-mode selection to select Football and Rugby. (in the AndGame field)

                           

                          AndGame is connected to Unique_ID, and my selection of AndGame makes 4 Unique_ID possible.

                           

                          These 4 Unique_ID exists in 9 different records (as shown in the right lower table), so Count(Unique_ID) will show a total of 9, but Count(Distinct Unique_ID) will show a total of 4.

                           

                          /gg

                          • Re: *** Sports Analysis (And-Selection) Mode ***
                            Stefan Wühl

                            Hi D V,

                             

                            I think you could use set analysis instead, something along:

                            =count(distinct {<Unique_ID= p({<Game = {'Cricket'}>}) * p({<Game = {'Football'}>}) >}Unique_ID)

                             

                            Though here, your selected values are hard coded. But I just posted a sample app here to have listboxes as input to the set analysis set elements.

                             

                            Hope this helps,

                            Stefan

                             

                            P.S. Regarding the DISTINCT, I don't get the question, sorry?

                              • Re: *** Sports Analysis (And-Selection) Mode ***
                                Deepak Vadithala

                                Kaushik & gg - Thanks again for taking time to look into my issue.

                                 

                                Stefan - Big thanks to you for your ideas. Please ignore my previous question, gg has clarified the same and I am happy with his response. Coming back to the original issue... not sure if I was clear in my previous message. But your first solution work (Using the And-mode). However, I am little confused with your most recent message. Indirect Analysis is clearly good idea but it will not work without enabling the And-mode... is this right? Please correct me if I am wrong.  I had looked at both the threads you are referring and it shows that you have still used And-mode with indirect analysis.

                                 

                                The reason why I don't want to use the And-mode is because I need to show both OR-mode and And-mode in the same application. And you can't use both the modes in the same document because it will disable other one. So I thought of a work around to reload the same data with different field names but this is not ideal work around (because of the volume of data). Hence, I am looking for a method using Set Analysis and without enabling the And-mode. Please let me know if this is possible?

                                 

                                I hope this makes sense now.

                                 

                                I'll look forward for your thoughts...

                                 

                                I'd love to hear more ideas on this issue

                                 

                                Cheers - DV

                                  • Re: *** Sports Analysis (And-Selection) Mode ***
                                    Goran Korsgren

                                    Hi DV

                                     

                                    Well you can definitely have both 'normal' and 'and mode' searches on the game field in the same qvw.

                                    You just need to have separate fields. Try the attached qvw.

                                     

                                    andmodesports.JPG

                                    As yo can see I have here filtered on the 'normal' field Game and this shows all combinations that has seen Football OR Rugby.

                                     

                                    hth/gg

                                    • Re: *** Sports Analysis (And-Selection) Mode ***
                                      Stefan Wühl

                                      Hi D V,

                                       

                                      in the thread I referenced, I posted an app where I believe I don't use AND mode with list boxes (i.e. the one you activate as option in the properties).

                                      It might be confusing that we were still talking about AND mode, because that's what we wanted to achieve without using the AND mode option provided by QV. And I think we have achieved that.

                                       

                                      So, we got a AND mode selection list box, without using AND mode option in the properties, but using set analysis in the charts expressions.

                                       

                                      In my latest post to that thread, you will notice, that I used as well AND and OR logics to select on Brands.

                                       

                                      And I'm not 100% sure, but I think you could also use AND mode list boxes (with option activated in the properties) and other list boxes with OR mode (for example, just add a Game field list box to my sample app).

                                       

                                      If you need more information about the AND mode samples referenced in my link, just ask

                                       

                                      Regards,

                                      Stefan

                                  • Re: *** Sports Analysis (And-Selection) Mode ***
                                    Kaushik Solanki

                                    Hi,

                                     

                                        I also checked and the expression i have given you is not working.

                                     

                                        Here is the second expression which worked for me.

                                     

                                        if(Count({<Game = {"Cricket","Tennis"} >}Unique_ID) = 2,Unique_ID)

                                     

                                        This might not be the proper way to do this, but it works.

                                     

                                    Regards,

                                    Kaushik Solanki

                            • Re: *** Sports Analysis (And-Selection) Mode ***
                              Kaushik Solanki

                              Hi,

                               

                                  Try this expression

                               

                                  =count({<Game = {"Cricket"} * {"Football"} >}Unique_ID)

                               

                               

                                 Here in set analysis * means "AND" according to your requirement. Meaning it will contain the customers which have both Cricket and Foodball as Game.

                               

                              Regards,

                              Kaushik Solanki

                              • Re: *** Sports Analysis (And-Selection) Mode ***

                                Hi Deepak,

                                 

                                I found this post very useful in my current work.

                                 

                                I just wanted to know based on youe example only with Games if there was another column say Game Type and we needed to do a AND/NOT slection on that column too.

                                How should my approach be if we need to generate Output based on two or more columns AND/NOT selection.

                                 

                                Any Ideas Please.

                                 

                                Thanks in advance.

                                 

                                Regards,

                                Pooja