topic Re: What are the differences between these three set analyses? in QlikView

What are the differences between these three set analyses?

baylor2016 — Tue, 05 Sep 2017 12:49:00 GMT

Hi,

I am working on a chart using set analysis and do not know why the following two give different results. What I am trying to do is to combine the union and intersection to get the number I need. If I can figure out this, my problem should be solved.

Thanks

This one returns 940

=count(DISTINCT
{<
YearMonth,
FLAG_IVD={1},
CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},
PAT_ID = {"=Age(Num(V_selectedDate), Only({<YearMonth>}BIRTH_DATE)) >= 18"},
VISITTYPE={'Office visit'},
FCMVISIT={1}
>}
PAT_ID)

This one returns 118

count(DISTINCT
{
<FLAG_IVD={1}, YearMonth>*
<
YearMonth,
CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},
PAT_ID = {"=Age(Num(V_selectedDate), Only({<YearMonth>}BIRTH_DATE)) >= 18"},
VISITTYPE={'Office visit'},
FCMVISIT={1}
>
}
PAT_ID)

This one only returns 1

count(DISTINCT
{
<FLAG_IVD={1}>*
<
YearMonth,
CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},
PAT_ID = {"=Age(Num(V_selectedDate), Only({<YearMonth>}BIRTH_DATE)) >= 18"},
VISITTYPE={'Office visit'},
FCMVISIT={1}
>
}
PAT_ID)

Re: What are the differences between these three set analyses?

vinieme12 — Tue, 05 Sep 2017 13:09:07 GMT

Refer this link

https://www.resultdata.com/qlikview-set-analysis-guide/

Re: What are the differences between these three set analyses?

baylor2016 — Tue, 05 Sep 2017 14:31:49 GMT

Hi Vineeth,

Thank you for the reference.

I know the basics and read the article you referred before. I would like to know the differences between these three since the results are so different. I cannot figure out why.

Longmatch

Re: What are the differences between these three set analyses?

Peter_Cammaert — Tue, 05 Sep 2017 14:48:13 GMT

It should be pretty simple to find out, if only we got the underlying fields and data.

On the face of it, I can only say that you are creating different resulting sets. The first one is the most inclusive because it combines all set specifications together. The second one appears to be more restrictive as you are making the intersection of two separate sets (which the first one didn't do). The third one creates the smallest intersection between two sets with few items in common.

Note that you can display the results of each set in a straight table, and manually figure out what is happening behing the set analysis screens.

Re: What are the differences between these three set analyses?

Peter_Cammaert — Tue, 05 Sep 2017 14:51:08 GMT

Also do not forget that the current set of selections can have a considerable impact on the outcome of each set specification. And some set modifiers are resetting current selections in one set but not in the other.

Re: What are the differences between these three set analyses?

baylor2016 — Tue, 05 Sep 2017 14:54:04 GMT

Hi Peter,

I am expecting the first one and second one to get the same number since they all are intersections. I tested the intersection with one small example attached, but it does not work here. I don't know why.

Thanks

Longmatch

Re: What are the differences between these three set analyses?

sunny_talwar — Tue, 05 Sep 2017 15:39:42 GMT

What is not working here?

Only patient ID 1 meets the criteria and both expression gives 1? Isn't that right?

Re: What are the differences between these three set analyses?

baylor2016 — Tue, 05 Sep 2017 15:46:26 GMT

Hi Sunny,

The sample works fine, but the set analysis does not work in my project.

Here is another example

The scripts below returns 118, but the first part and second part return 940 and 35 respectively. It looks like the union does not work here. I really cannot figure it out.

Thanks.

Longmatch

=count(DISTINCT
{

//Returns 940
<
FLAG_IVD={1},
YearMonth,
CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},
PAT_ID = {"=Age(Num(V_selectedDate), Only({<YearMonth>}BIRTH_DATE)) >= 18"},
VISITTYPE={'Office visit'},
FCMVISIT={1}
>
+

//Return 35
<
YearMonth,
FLAG_AMI={1},
CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},
PAT_ID = {"=Age(Num(V_selectedDate), Only({<YearMonth>}BIRTH_DATE)) >= 18"},
VISITTYPE={'Office visit'},
FCMVISIT={1}
>
}
PAT_ID)

Re: What are the differences between these three set analyses?

sunny_talwar — Tue, 05 Sep 2017 15:49:14 GMT

May be create a chart with

Dimension

PAT_ID

Expressions

=Count(DISTINCT
{

=Count(DISTINCT
{<
YearMonth,
FLAG_AMI={1},
CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},
PAT_ID = {"=Age(Num(V_selectedDate), Only({<YearMonth>}BIRTH_DATE)) >= 18"},
VISITTYPE={'Office visit'},
FCMVISIT={1}
>
}
PAT_ID)

=Count(DISTINCT
{

and see which PAT_IDs are not getting counted by your third expression

Re: What are the differences between these three set analyses?

baylor2016 — Tue, 05 Sep 2017 16:01:28 GMT

IVD	AMI	IVD OR AMI
1	1	0
1	0	1
1	0	0

I just create a straight table using three expressions. This is what I found (see above)

The results in Excel format is attached. Something is not right.

Thanks

Re: What are the differences between these three set analyses?

sunny_talwar — Tue, 05 Sep 2017 16:25:50 GMT

This is strange for sure... would you be able to share a qvw to investigate more?

Re: What are the differences between these three set analyses?

baylor2016 — Tue, 05 Sep 2017 16:28:33 GMT

Yes. I will get it ready soon.

Re: What are the differences between these three set analyses?

vinieme12 — Wed, 06 Sep 2017 07:27:49 GMT

You can validate the output manually for each of your set expressions, that will help you understand what exactly you are getting.

Download data For the first expression:

break the below in two expressions and download data for each set separately and verify

remember * is the intersection, so use vlookup to check what PAT_ID were common between both sets

that is the output you are getting

Expressions (Disable the other expression alternately before downloading the data)

a) count(DISTINCT {<FLAG_IVD={1}, YearMonth> }PAT_ID)

b)) count(DISTINCT { <

YearMonth,

CONTACT_DATE2 = {"$(='>=' & Date(V_selectedDate_3YearAgo) & '<=' & Date(V_selectedDate))"},

PAT_ID = {"=Age(Num(V_selectedDate), Only({<YearMonth>}BIRTH_DATE)) >= 18"},

VISITTYPE={'Office visit'},

FCMVISIT={1}

>}PAT_ID)

As above break into two expressions and download data for each expression individually and verify what values are returned