Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Bar Charts and Accumulation

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)))

1 Solution

Accepted Solutions
sunny_talwar

May be this:

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


Capture.PNG



View solution in original post

5 Replies
sunny_talwar

You want this?

Capture.PNG

Expression for 2nd column

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

Anonymous
Not applicable
Author

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

May be this:

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


Capture.PNG



Anonymous
Not applicable
Author

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.