Calculating Cumulative Percentage of Users Who Have Satisfied a Condition

[ssssssss88888888]

Hello,  I'm an absolute beginner at Qlik Sense so I apologize for that in advance. I have a basic spreadsheet I have ported in to Qlik that contains three columns: ID, Name and Date. What I would like to do is see whose name appears on this list more than 4 times. Then, I would like to create some script/visualization that indicates how many people have satisfied this condition of appearing more than 4 times. Since there are dates included, this process would happen over time; as the year went on, more and more people would eventually satisfy the condition. Ideally, I would like to see that represented as a line that goes up over time. It would start at 0, and in the case of this data set, would eventually end up at 33 percent (3 of the 9 people satisfy the condition by EOY). I'm looking to do this for a larger data set but need to understand the mechanics of how to do this before I do so. Thank you so much for your assistance!

[edwin · Accepted solution]

i would create a bridge table that will implement your cumulative.  it does it by associating all Dates prior to a Month so that if you use that Month field, you are summing all dates prior to it.  the logic is lke this:

Date	Month
1/1/2022	1/1/2022
1/1/2022	2/1/2022
2/1/2022	2/1/2022
1/1/2022	3/1/2022
2/1/2022	3/1/2022
3/1/2022	3/1/2022

so if 2/1/2022 Month is selected, 1/1/2022 and 2/1/2022 will be counted and so on.

NoConcatenate
data:
load ID, Name, monthstart(Date) as Date inline [
ID,Name,Date
1,Jeff,10/12/2022
2,Gerard,9/9/2022
3,Scott,7/7/2022
4,Steve,7/7/2022
5,Mike,5/2/2022
6,Alice,5/5/2022
7,Alice,2/2/2022
8,Alice,5/5/2022
9,Alice,5/6/2022
10,Mike,6/6/2022
11,Steve,2/2/2022
12,Scott,2/2/2022
13,Scott,3/3/2022
14,Scott,3/3/2022
15,Scott,5/5/2022
16,Derek,2/2/2022
17,Bill,2/2/2022
18,Bill,3/3/2022
19,Bill,4/4/2022
20,Susan,7/7/2022
21,Scott,10/10/2022
22,Gerard,10/10/2022
23,Mike,1/1/2022
24,Mike,2/22/2022
25,Mike,4/4/2022
26,Mike,10/10/2022
27,Mike,11/11/2022
28,Mike,11/12/2022
];

NoConcatenate
tmp:
load distinct Date Resident data;

inner join (tmp)
load Date as Month Resident tmp;

NoConcatenate
Bridge:
load Month, Date
Resident tmp
where Month>=Date;

drop table tmp;

in your table or chart: 

[ssssssss88888888]

thank you Edwin!! Really appreciate your help!