Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
danilopatro
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_CREATIONUNIQUE_PROCESS_NUMBER
06/06/20131136.302
09/06/20131064.972
19/07/20131125.427
20/07/20131105.555
27/07/20131168.205
10/09/20131078.854
23/09/20131049.557
15/10/20131099.077
25/10/20131211.117
08/12/20131070.155
22/12/20131185.186
27/12/20131182.244
30/12/20131237.056
19/01/20141231.838
27/03/20141155.935
30/03/20141019.095

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

Something like this:

MonthCumulative Counting
jun/20132
jul/20135
aug/20135
sep/20137
oct/20139
nov/20139
dec/201313
jan/20141
feb/20141
mar/20143
apr/20143
may/20143
jun/20143

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:

 

MonthCumulative Counting
jun/20132
jul/20133
aug/20130
sep/20132
oct/20132
nov/20130
dec/20134
jan/20141
feb/20140
mar/20142
apr/20140
may/20140
jun/20140
1 Solution

Accepted Solutions
danilopatro
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
william_fu
Contributor II

Re: Cumulative counting

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

Re: Cumulative counting

You can also try this

RangeSum(Above(

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

, 0, RowNo()))

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

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

Re: Cumulative counting

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

danilopatro
New Contributor II

Re: Cumulative counting

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

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

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