# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
New Contributor II

## Cumulative counting

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
1 Solution

Accepted Solutions
New Contributor II

## Re: Cumulative counting

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

8 Replies
Contributor II

## Re: Cumulative counting

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

MVP

## Re: Cumulative counting

You can also try this

RangeSum(Above(

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

, 0, RowNo()))

New Contributor II

## Re: Cumulative counting

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

New Contributor II

## Re: Cumulative counting

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.

MVP

## Re: Cumulative counting

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

New Contributor II

## Re: Cumulative counting

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

New Contributor II

## Re: Cumulative counting

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

New Contributor II

## Re: Cumulative counting

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