topic Re: Bar Charts and Accumulation in QlikView

Bar Charts and Accumulation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Bar Charts and Accumulation

sunny_talwar — Fri, 22 Jul 2016 15:06:56 GMT

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

Re: Bar Charts and Accumulation

Anonymous — Fri, 22 Jul 2016 16:51:29 GMT

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

Re: Bar Charts and Accumulation

sunny_talwar — Fri, 22 Jul 2016 17:01:21 GMT

May be this:

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

Re: Bar Charts and Accumulation

Anonymous — Fri, 22 Jul 2016 17:42:39 GMT

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

Re: Bar Charts and Accumulation

sunny_talwar — Fri, 22 Jul 2016 17:44:47 GMT

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.