Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

IAMDV
Honored Contributor II

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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

15 Replies
MVP
MVP

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

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

IAMDV
Honored Contributor II

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

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 ***

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

MVP
MVP

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

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.

IAMDV
Honored Contributor II

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

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

gandalfgray
Valued Contributor

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

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

MVP
MVP

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

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 ***

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

IAMDV
Honored Contributor II

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

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

Community Browser