Chart - Expression: Count and Accumulate when

[Anonymous]

Sample data below and sample app attached.

I'm trying to measure "Engaged Members" Per Month (& create a % change per month in a como chart but need to understand how I would create the counts first).

Metric:

Per month: Count member id and when member is still "Open" accumulate to the following month.

The below data should read

201607 = 10 (Memberid: 1-9 & 21)

201606 = 8 (Memberid: 13-19 & 21)

Total records below = 17

[Gysbert_Wassenaar]

See attached example.

[Anonymous]

That's close, but for my chart my results are backwards. See below pic. The accumulation total should be larger througout time. I simply tried to sort ASC but that didn't work. I also tried to swap above with below and that didn't seem to work either. I do only want to visually see 6 values but calculate all. So, I added the if(rowNo()<=6

Any suggestions?

if(RowNo()<=6,
rangesum(
COUNT(ID),
sum(aggr(rangesum(above(COUNT({<ENROLLMENT_STATUS={'OPEN'},STARTDATE_YYYYMM>}ID), 1, RowNo(total))),STARTDATE_YYYYMM))
)
)

[Gysbert_Wassenaar]

Read the script. It explains how the months are sorted. If you want only the last six month then select those months or add a condition to the expression that selects those months. Something like this:

rangesum(

  COUNT({<START_Year_Month={'>=$(=max(START_Year_Month)-5)<=$(=max(START_Year_Month))'}>}Memberid),

  sum({<START_Year_Month={'>=$(=max(START_Year_Month)-5)<=$(=max(START_Year_Month))'}>}aggr(rangesum(above(COUNT({<Status={'OPEN'},START_Year_Month>}Memberid), 1, RowNo(total))),START_Year_Month))

)

Note, this won't work correctly if you pass a year border. 201605 - 5 = 201600 instead of 201512. What you need to do to fix that is to use a real date field instead of the ordinal numeric yearmonth field.

[vinieme12]

Try the attached

[Anonymous]

I'll try and work it out on my own and be more clear as to what I'm looking for becuase either I'm not following which is probable or we are not in synch.  Thanks!