topic Re: Cummulative Count Starting from nth Month in QlikView

Cummulative Count Starting from nth Month

nikita42 — Sat, 16 Nov 2024 04:59:50 GMT

I need to create a straight table with a cumulative count of IDs for the past 6 months. So today that would be the count from Jul-2018 to Dec-2018. The issue is that the July count should include the total of all previous months as well (but those previous months should not be displayed on the table). I can't use rangesum since that would require all months to be displayed and the set analysis doesn't work since the Month field I need to filter on is also being displayed on the table.

Table should Look something like this. Top row is the regular count of IDs, Rolling is the cumulative count with the first column including all previous months:

Jul Aug Sep Oct Nov Dec

count 10 5 5 10 1 0

Rolling 50 55 60 70 71 71

Any ideas?

Re: Cummulative Count Starting from nth Month

sunny_talwar — Tue, 11 Dec 2018 12:48:41 GMT

@nikita42 wrote:
I can't use rangesum since that would require all months to be displayed and the set analysis doesn't work since the Month field I need to filter on is also being displayed on the table.

This is not true... if you ignore selection in month field.... you should be able to accumulate even for the months not displayed.... for example...

RangeSum(Above(Sum({<MonthField>} Measure), 0, RowNo())) * Avg(1)

Here we use Avg(1) to display the months which are selected as Avg(1) = 1 for selected months and 0 for non selected months.

Re: Cummulative Count Starting from nth Month

nikita42 — Tue, 11 Dec 2018 13:18:07 GMT

My month field is part of the set analysis to restrict the count to the last 6 months. So my expression is something like Count({<Month={">=$(=AddMonths(Max(Month),-6))"}>}Id)

I can't ignore the month AND use it in set analysis, can I?

Re: Cummulative Count Starting from nth Month

MarcoWedel — Tue, 11 Dec 2018 21:59:44 GMT

see also:
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

hope this helps

regards

Marco

Re: Cummulative Count Starting from nth Month

sunny_talwar — Wed, 12 Dec 2018 12:47:54 GMT

Yes, you can... try something like this

RangeSum(Above(Sum({<Month>} Measure), 0, RowNo()))
*
Avg({<Month={">=$(=AddMonths(Max(Month),-6))"}>} 1)