Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
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