topic Re: Re: Venn diagram (Google API) in QlikView

Venn diagram (Google API)

Thu, 24 Apr 2014 08:31:09 GMT

Hello community,

I hope you can help me with a topic i know has been covered off in different ways.

First of all, thanks to all the people who have responded in the past to Venn Diagram queries a lot of this as been mega helpful and taught me lot of useful Set Analysis. The Google Venn is a very powerful API and i hope one day QlikTech will implement it into a release.

So, on to my issue.

I have a successfull Venn diagram in my application, and i understand the variables which are needed to be passed though. the problem i am having is how to calulcate the intersects of my data.

I have a simple yet large dataset of 13M records. This is a fact table of outbound Campaigns. EG:

[BP] [Campaign_Name] [Date]

BP is the customer ID.

My application only allows the venn diagram to work only on 3 selected letters (there could be 50 to choose from). The first three variables of the Venn API are the total size of each circle ive called these , i've used a combination on variables to calulate this.

Current selection variables (This will return the String of the selected campaign names)

vVennSelect1 vVennSelect2 vVennSelect3:

trim(SubField(GetFieldSelections(Campaign_Name),',',1))

trim(SubField(GetFieldSelections(Campaign_Name),',',2))

trim(SubField(GetFieldSelections(Campaign_Name),',',3))

Total count variables (Using the variables above to give me a count of the BP's)

vVennCount1 vVennCount2 vVennCount3:

count({$<Campaign_Name={'$(vVennSelect1)'}>} BP)

count({$<Campaign_Name={'$(vVennSelect2)'}>} BP)

count({$<Campaign_Name={'$(vVennSelect3)'}>} BP)

The next part i'm struggling on. The above is really my limit on Set Analysis, so i really want to understand how from one table can i return an intersect from a selection A+B B+C C+A - the customer ID is the primary key here.

Is there an expression i can use in a variable to return the above intersects.

Help here would be really appreciated.

Thanks for looking

Re: Venn diagram (Google API)

Thu, 24 Apr 2014 09:19:28 GMT

Matthew,

may you share qvw with sample data and examples of expected results you would like to get for few selection cases?

regards

Darek

Re: Re: Venn diagram (Google API)

Thu, 24 Apr 2014 10:37:34 GMT

Hi Dariusz Mielczarek and thanks for the speedy reply

i have attached an exmaple as best i can with an inline table (but no master calendar) and a venn diagram

the mentioned variables are in there too.

the Venn Expression contains hard coded numbers at the moment, taken from the variables, these are in blue boxes to see if they are passin through.

Re: Re: Venn diagram (Google API)

Thu, 24 Apr 2014 12:09:03 GMT

Matthew,

this will give you intersections:

a) A+B (number of BP, which are present in your table with CAMPAIGN1 and CAMPAIGN2):

sum(aggr(if(count(DISTINCT {<Campaign_Name={'$(=vVennSelect1)','$(=vVennSelect2)'}>}Campaign_Name)=2,1,0), BP))

b) B+C

sum(aggr(if(count(DISTINCT {<Campaign_Name={'$(=vVennSelect3)','$(=vVennSelect2)'}>}Campaign_Name)=2,1,0), BP))

c) A+C

sum(aggr(if(count(DISTINCT {<Campaign_Name={'$(=vVennSelect3)','$(=vVennSelect1)'}>}Campaign_Name)=2,1,0), BP))

Of course you must check if there are 2 or 3 campaigns selected. If only 2, you should pass 0 or no value to google api for A+C intersection.

let me know if it helps

Regards

Darek

Re: Re: Venn diagram (Google API)

Thu, 24 Apr 2014 12:25:58 GMT

ahh,

you need also A+B+C intersection, i think

c) A+B+C

sum(aggr(if(count(DISTINCT {<Campaign_Name={'$(=vVennSelect3)','$(=vVennSelect2)','$(=vVennSelect1)'}>}Campaign_Name)=3,1,0), BP))

Re: Re: Re: Venn diagram (Google API)

Thu, 24 Apr 2014 12:48:13 GMT

look into attachment.

Re: Re: Venn diagram (Google API)

Thu, 24 Apr 2014 13:59:41 GMT

THIS IS JUST AMAZING!

You have solved my question, thanks so much.

I know this is a large ask, and an optional on your part Dariusz, is there any chance you could explaing why and how that expression works please?

Re: Re: Venn diagram (Google API)

Thu, 24 Apr 2014 14:19:32 GMT

of course, i can explain how does it works.

If you want, I may even come and help you in your QlikView projects

Anyway, this case, on intersection 1 and 2:

=sum(aggr(if(count(DISTINCT {<Campaign_Name={'$(=vVennSelect1)','$(=vVennSelect2)'}>}Campaign_Name)=2,1,0), BP))

lets start from aggr( expression , BP)

this will calculate expression for each BP

our expression is:

if(count(DISTINCT {<Campaign_Name={'$(=vVennSelect1)','$(=vVennSelect2)'}>}Campaign_Name)=2,1,0)

so:

DISTINCT {<Campaign_Name={'$(=vVennSelect1)','$(=vVennSelect2)'}>}Campaign_Name

will give you rowset with distinct walues of Campaign_Name (remember, that we are on BP level, and we have condition <Campaign_Name={'$(=vVennSelect1)','$(=vVennSelect2)'}>)}>.

we count number ow rows in this rowset.

To know, that this BP is intersection, we need exactly 2 rows. This is why if count=2 then 1 else 0.

So, after taht we have for each BP value 0 (if it is not in intersection) or 1 (if it is in itersection).

If we sum those values, we will get number of BP in our intersection

regards

Darek

Re: Re: Venn diagram (Google API)

Thu, 24 Apr 2014 14:55:55 GMT

Thanks again, i had little parts of that expression dotted around, but its knowing how to apply it or put it in place. this set analysis is proving quite powerfull, the developer course i went on didnt cover it a huge amount.

Thank you again