Cumulative count (out of selection) in qlikview chart expression

[#1]

Hello,

I would like to count materials from the beginning (January 2010) even if I select ONLY year 2017.

In other words, if I select ONLY 2017’s month, I would like to have:

- For January 2017: Cumulative Count of materials From January 2010 To  January 2017.

- For February 2017: Cumulative Count of materials From January 2010 To  February 2017.

- For March 2017: Cumulative Count of materials From January 2010 To  March 2017.

PFA file for more details.

Thx in advance for your help.

Ricky.

[#2]

I m using V11.

Yes, I have the problem even with your app.

[sunny_talwar]

Can you try this

If(Month > 0,  RangeSum(Above(Count({<Month>}Material), 0, RowNo())))

[#4]

It also doesn't work unfortunately.

I have the right values, but just I can't hide unused months :-(.

[sunny_talwar]

I think the only option left for you is to upgrade to a newer version of QV because this looks more like a qv version issue.

[#6]

OK Thx.

It was very helpful neverthless.

[sunny_talwar]

One more way to do this is usingThe As-Of Table‌

Table1:

LOAD Date(Date#(Month, 'YYYYMM'), 'YYYYMM') as Month,

  Material;

LOAD * INLINE [

    Month,Material

    201001,MAT1

    201602,MAT2

    201602,MAT3

    201604,MAT4

    201607,MAT5

    201701,MAT6

    201702,MAT7

    201703,MAT8

];

Min:

LOAD Min(Month) as MinMonth

Resident Table1;

LET vMin = Peek('MinMonth');

DROP Table Min;

AsOfTable:

LOAD Month as AsOfMonth,

  Date(AddMonths(Month, (-IterNo()+1)), 'YYYYMM') as Month

Resident Table1

While AddMonths(Month, (-IterNo()+1)) >= $(vMin);

[#8]

I gave you assignment (right answer).

[#9]

It's a very good way, but I'm afraid of bad performance, because I will have more that 10 millions records.

If I understand, we duplicate material rows according the number of correspondant month.

[sunny_talwar]

Material is not duplicated, only each Month is associated with all the previous month in the script. This will give you a way better performance then RangeSum() because the heavy lifting is done in the script.