Cummulative Count Starting from nth Month

[nikita42]

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?

[sunny_talwar]

---

@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.

[nikita42]

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?

[MarcoWedel]

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

hope this helps

regards

Marco

[sunny_talwar]

Yes, you can... try something like this

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