topic Re: * Sports Analysis (And-Selection) Mode * in QlikView

* Sports Analysis (And-Selection) Mode *

IAMDV — Mon, 12 Sep 2011 09:08:54 GMT

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 *

swuehl — Mon, 12 Sep 2011 09:27:13 GMT

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 *

IAMDV — Mon, 12 Sep 2011 09:43:25 GMT

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 *

kaushiknsolanki — Mon, 12 Sep 2011 09:51:42 GMT

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 *

swuehl — Mon, 12 Sep 2011 09:55:08 GMT

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 *

IAMDV — Mon, 12 Sep 2011 11:19:42 GMT

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,

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

gandalfgray — Mon, 12 Sep 2011 12:16:48 GMT

Hi DV

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

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 *

swuehl — Mon, 12 Sep 2011 12:18:49 GMT

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 *

kaushiknsolanki — Mon, 12 Sep 2011 12:44:42 GMT

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 *

IAMDV — Mon, 12 Sep 2011 13:45:50 GMT

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 *

gandalfgray — Mon, 12 Sep 2011 14:00:28 GMT

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.

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 *

swuehl — Mon, 12 Sep 2011 14:02:26 GMT

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 *

swuehl — Mon, 12 Sep 2011 14:17:42 GMT

Hi D V,

I've just adapted your app to my proposed solution using set analysis - no AND mode list boxes using QV option.

Please find attached.

Regards,

Stefan

P.S: the chart using the set expression is the one on the left, ignore the one in the middle.

edit: Corrected attached app

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

IAMDV — Mon, 12 Sep 2011 14:32:16 GMT

GandalfGray - Thank you very much for your help. That's nice to know... I thought I have to reload whole data model instead of one field. This is very helpful. Your answer is also correct, however I can only mark one of them as "Correct". But I gave you 5 points for helpful answer and once again thanks for your help.

Stefan - Many thanks for your expert suggestions on this issue. I had never used the And-mode in the past and this is very good learning for me. I am impressed by your solution in the sample reference link, which uses the indirect analysis in string builder. Although, I will keep it simple for now and I will use the And-mode (listbox properties) and load additional field as recommended by gg & you. But I will deep dive your referenced example and I would come back to with more questions.

Cheers - DV

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

gandalfgray — Tue, 13 Sep 2011 07:23:52 GMT

Hi DV!

Your welcome!

GandalfGray

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

Wed, 15 Jan 2014 09:28:13 GMT

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

topic Re: *** Sports Analysis (And-Selection) Mode *** in QlikView

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

topic Re: * Sports Analysis (And-Selection) Mode * in QlikView

* Sports Analysis (And-Selection) Mode *

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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