Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
@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))))