Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Refer this link
https://www.resultdata.com/qlikview-set-analysis-guide/
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
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.
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.
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
What is not working here?
Only patient ID 1 meets the criteria and both expression gives 1? Isn't that right?
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)
May be create a chart with
Dimension
PAT_ID
Expressions
1)
=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}
>}
PAT_ID)
2)
=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)
3)
=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)
and see which PAT_IDs are not getting counted by your third expression
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