Cumulative counting

[Anonymous]

Hi everyone, I'm a newbie at Qlik and I'm facing some difficulties into counting cumulative number of registers by month during a year.

I have a table like this below that was imported from EXCEL:

DATE_CREATION	UNIQUE_PROCESS_NUMBER
06/06/2013	1136.302
09/06/2013	1064.972
19/07/2013	1125.427
20/07/2013	1105.555
27/07/2013	1168.205
10/09/2013	1078.854
23/09/2013	1049.557
15/10/2013	1099.077
25/10/2013	1211.117
08/12/2013	1070.155
22/12/2013	1185.186
27/12/2013	1182.244
30/12/2013	1237.056
19/01/2014	1231.838
27/03/2014	1155.935
30/03/2014	1019.095

I need just to calculate the number of process cumulative through the year for each.

Something like this:

Month	Cumulative Counting
jun/2013	2
jul/2013	5
aug/2013	5
sep/2013	7
oct/2013	9
nov/2013	9
dec/2013	13
jan/2014	1
feb/2014	1
mar/2014	3
apr/2014	3
may/2014	3
jun/2014	3

I used for the dimension the following expression:

=[DATE_CREATION.autoCalendar.YearMonth]

For the measure I used the following expression:

=count(IF(InYearToDate([DATE_CREATION],monthEnd([DATE_CREATION]),0), [UNIQUE_PROCESS_NUMBER]))

However, the result it's just the number of [UNIQUE_PROCESS_NUMBER] by month like below:

Month	Cumulative Counting
jun/2013	2
jul/2013	3
aug/2013	0
sep/2013	2
oct/2013	2
nov/2013	0
dec/2013	4
jan/2014	1
feb/2014	0
mar/2014	2
apr/2014	0
may/2014	0
jun/2014	0

[william_fu]

If you're willing to do some script editing, this is a great solution: The As-Of Table

[sunny_talwar]

You can also try this

RangeSum(Above(

Count(If(InYearToDate([DATE_CREATION], MonthEnd([DATE_CREATION]), 0), [UNIQUE_PROCESS_NUMBER]))

, 0, RowNo()))

[Anonymous]

Thanks williamfuu‌ but I'm trying to do this as an expression on a straight table at the graphics panel.

[Anonymous]

Thanks stalwar1, but seems that your proposal could implies into a infinite sum while the row number grows, other issue it's when the year change in which the counting need to be reset. I tried to solve this replacing RowNo() by Month(Min([DATE_CREATION])), however when the table were not sorted by months, the calculating returned stranger values.

[sunny_talwar]

Have you created a field in the script for Month and Year?

[Anonymous]

When I loaded the data from EXCEL the Qlik created the [autoCalendar] fields, including month,year among others.

[Anonymous · Accepted solution]

Hi everyone,

just to don't let this thread without answer, I found the solution using the code below as a master item and adding to the straight table:

Aggr(RangeSum(Above(Count({1}[UNIQUE_PROCESS_NUMBER]), 0, RowNo())), ([DATE_CREATION.autoCalendar.Year], NUMERIC, ASCENDING),([DATE_CREATION.autoCalendar.Month], NUMERIC, ASCENDING))

[One_day_I_will_know]

Hi,

I just found your question and answer, thank you for providing them! Works great, allthough I don't know why. Couldn't build it by myself (a real newbie).