topic Re: Chart - Expression: Count and Accumulate when in QlikView

Chart - Expression: Count and Accumulate when

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

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

Re: Chart - Expression: Count and Accumulate when

Gysbert_Wassenaar — Thu, 21 Jul 2016 06:29:25 GMT

See attached example.

Re: Chart - Expression: Count and Accumulate when

Anonymous — Thu, 21 Jul 2016 11:52:55 GMT

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

Re: Chart - Expression: Count and Accumulate when

Gysbert_Wassenaar — Thu, 21 Jul 2016 11:54:40 GMT

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.

Re: Chart - Expression: Count and Accumulate when

vinieme12 — Thu, 21 Jul 2016 13:21:49 GMT

Try the attached

Re: Chart - Expression: Count and Accumulate when

Anonymous — Thu, 21 Jul 2016 14:09:15 GMT

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!