Bar Charts and Accumulation

[Anonymous]

I'm looking to create a chart with an expression that captures a count of YYYYMM (not in date format) of a member id.

A. Count all members for that Month

B. Also accumulate members for all of the previous months that have an enrollment status of Open.

C. Chart layout the latest 6 months.

D. 201607 out put should be 49,755, 201606 output should be 48,199

201607    201606        201605        201604    201603    201602

49,755     48,199

Attached is sample data and sample output of what I would like with a pivot table. Also, can someone please explain what the expression is doing. When we start talking about Aggr(), RangeSum, RowNo() I start to lose track of what's going on.

I have similar charts with below formulas from others that have helped me on here but I cannot duplicate.

=If(RowNo() <= 6, RangeSum(Below(Count({<} >} ), 0, NoOfRows() - RowNo() + 1)))

[sunny_talwar]

You want this?

Expression for 2nd column

=If(STARTDATE_YYYYMM >= Max(TOTAL STARTDATE_YYYYMM, 2), Above(RangeSum(Above(Count({<Status = {'Open'}>}id), 0, RowNo()))) + Count(id))

[Anonymous]

The counts are correct but I would like a rolling 6 values for Start_date_YYYYMM and starting from Left to right.

I would like to see the chart like below

49,755      48,199       44,2134   46,664      36,791      33,715

201607   201606        201605    201604     201603      201602

[sunny_talwar · Accepted solution]

May be this:

=If(STARTDATE_YYYYMM >= Max(TOTAL STARTDATE_YYYYMM, 6), Below(RangeSum(Below(Count({<Status = {'Open'}>}id), 0, NoOfRows() - RowNo() + 1))) + Count(id))

[Anonymous]

Exactly! But this expression! I would like to learn more about complex expression (this is complex for me).

=If(STARTDATE_YYYYMM >= Max(TOTAL STARTDATE_YYYYMM, 6), Below(RangeSum(Below(Count({<Status = {'Open'}>}id), 0, NoOfRows() - RowNo() + 1))) + Count(id))

[sunny_talwar]

Breaking down into pieces ideally help in understanding what might this be doing. That's what I usually what I do when I see a problem.

Start with the inner most parts

Count(id) and

Count({<Status = {'Open'}>}id)

and try to build from there.