topic Re: Cumulative Count adding only new IDs in App Development

Cumulative Count adding only new IDs

xthojanalyst — Fri, 27 Sep 2024 21:19:32 GMT

I'm trying to do a cumulative count of IDs year over year, and only want to count if the person once if they appeared in the numerator or denominator throughout the year. Example of data below.

A person can fall in and out throughout the year. But once they're in the numerator, they will be counted as 1.

Person ID	Month Year	PersonID_MonthYr	Numerator	Denominator
A	Jan-20	A_Jan-20	0	1
B	Jan-20	B_Jan-20	0	1
C	Jan-20	C_Jan-20	1	1
A	Feb-20	A_Feb-20	1	1
B	Feb-20	B_Feb-20	0	1
C	Feb-20	C_Feb-20	1	1
A	Mar-20	A_Mar-20	1	1
B	Mar-20	B_Mar-20	1	1
D	Mar-20	D_Mar-20	0	1
A	Apr-20	A_Apr-20	1	1
B	Apr-20	B_Apr-20	1	1
C	Apr-20	C_Apr-20	1	1
D	Apr-20	D_Apr-20	0	1
B	May-20	B_May-20	1	1
C	May-20	C_May-20	1	1
D	May-20	D_May-20	0	1
D	Jun-20	D_Jun-20	1	1
E	Jul-20	E_Jul-20	0	1
F	Jul-20	F_Jul-20	1	1
C	Aug-20	C_Aug-20	1	1
D	Aug-20	D_Aug-20	1	1
E	Aug-20	E_Aug-20	1	1

My expression to create a cumulative count by month.

RangeSum(above(sum(aggr(count (distinct {$<[Denominator]={1}>} [PersonID_MonthYr]),[PersonID_MonthYr])), 0, month([Month Year]))).

I am getting close but it adds the distinct count of the full month to the cumulative count. I only want to add to the cumulative count to add each month for NEW IDs only.

This is a table of what the expression is doing and how I want it to calculate

Months	distinct IDs	Current Denominator Cumulation Expression	Desired Denominator Cumulation
Jan-20	3	3	3
Feb-20	3	6	3
Mar-20	3	9	4

In march, Person "C" dropped out and Person "D" has a record. The current expression adds all distinct IDs to the running count. It should only equal 4 for total new persons in the denominator. I want to do this for numerator also. Then I essentially want to create a rate from these two expressions, numerator cumulative expression/denominator cumulative expression.

I added "Month" in the expression because I want the cumulative counts to reset at the beginning of the year.

Re: Cumulative Count adding only new IDs

zar — Tue, 08 Oct 2024 19:32:12 GMT

Maybe not the best but technically may work. My result:

Table Dimension:

Chart expression:

Expression:

Count(Aggr(
FirstSortedValue(PersonID_MonthYr,
Aggr(RangeSum(Denominator,Numerator),[Person ID])
)
,[Person ID]))

Note: used modifier to have accumulate, also can use Above with this formula:

Used script to generate the data:

DATA:

LOAD

[Person ID],

MonthName(Date#([Month Year],'MMM-YY')) AS [Month Year],

PersonID_MonthYr,

Num#(Numerator) as Numerator,

Num#(Denominator) as Denominator

;

LOAD * INLINE [

Person ID Month Year PersonID_MonthYr Numerator Denominator

A Jan-20 A_Jan-20 0 1

B Jan-20 B_Jan-20 0 1

C Jan-20 C_Jan-20 1 1

A Feb-20 A_Feb-20 1 1

B Feb-20 B_Feb-20 0 1

C Feb-20 C_Feb-20 1 1

A Mar-20 A_Mar-20 1 1

B Mar-20 B_Mar-20 1 1

D Mar-20 D_Mar-20 0 1

A Apr-20 A_Apr-20 1 1

B Apr-20 B_Apr-20 1 1

C Apr-20 C_Apr-20 1 1

D Apr-20 D_Apr-20 0 1

B May-20 B_May-20 1 1

C May-20 C_May-20 1 1

D May-20 D_May-20 0 1

D Jun-20 D_Jun-20 1 1

E Jul-20 E_Jul-20 0 1

F Jul-20 F_Jul-20 1 1

C Aug-20 C_Aug-20 1 1

D Aug-20 D_Aug-20 1 1

E Aug-20 E_Aug-20 1 1

](delimiter IS '\t')

;

Re: Cumulative Count adding only new IDs

Kushal_Chawda — Wed, 09 Oct 2024 15:52:26 GMT

@xthojanalyst one more option is to use below expression

=sum(aggr(rangesum(above(sum(aggr( count( distinct {<Numerator={1}>+<Denominator={1}>} distinct PersonID),PersonID)),0,RowNo())),([Month Year],(NUMERIC))))