Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
ThePeterK
Creator
Creator

Count Distinct Possible Overlap

I am trying to count the number of students who registered for classes in a term who also registered for classes in the sequentially-previous term on the chart. The term ID numbers may have gaps in the sequence. 

ThePeterK_1-1615238110298.png

 

 

Example

Scenario 1: I have no filters.

In Term X-0, I see the current number of students who also registered in Term X-1,
In Term X-1, I see the number of students who were registered in Term X-1 that were also registered in Term X-2.
In Term X-2, I see the number of students who were registered in Term X-2 that were also registered in Term X-3
..and so on to Term X-X, the first term, where there were no students registered in a prior term, empty

Scenario 2: I have Terms filtered on Term 0, Term X-1 and Term X-3

In Term X-0, I see the current number of students who also registered in Term X-1
In Term X-1, I see the number of students who were registered in Term X-1 that were also registered in Term X-3.
In Term-3, where the prior term is filtered out, empty

This is a chart expression. What I have below is returning the current distinct count, including students who were not active in the previous 'active' term.

count(distinct
{<STUDENT_ID=
p(
{<TERM_ID={"=ABOVE(TERM_ID)"}>} STUDENT_ID
)
,REG_STATUS={'Active'}
>}
STUDENT_ID)

 

The formula above calculates to the same value as Count(distinct{<REG_STATUS={"Active"}>} STUDENT_ID).

 

How do I count the overlapping students in each term?

93/93
Labels (2)
0 Replies