Cummulative SUM with 2 dimensions

[Ceb]

Hi,

I have a table with Date, Group, Count, Cumulative Count(data below)

Date	Group	Count	Cummulative Count
01/01/1980	A	0	5115
09/01/1984	B	0	1364
01/01/1988	A	7	469
02/04/1988	A	0	2724
04/01/1988	A	1	392
06/01/1988	A	2	2903
05/01/1989	A	1	2972
07/15/1993	A	1	3046
08/01/1993	A	0	3580
11/01/1993	A	0	5328
11/30/1993	B	1	1423
12/08/1993	A	1	3774
12/13/1993	A	0	3728
12/15/1993	A	1	2926
12/31/1993	A	2	3682
01/01/1994	A	0	3368
01/10/1994	A	1	5107
02/02/1994	A	0	5315
02/08/1994	A	1	2935
02/08/1994	B	1	1293
03/29/1994	B	0	1307
04/10/1994	B	1	1955

For cummulative count I have tried the below code but I am getting some weird values

=aggr(Rangesum( above(
count (
[ID])
,0,RowNo())),Group,Date)

Referred: https://community.qlik.com/t5/New-to-Qlik-Sense/Rangesum-cumulative-with-2-dimensions/td-p/1285355

Though if I try without the grouping(aggr function) I get the right values. 

Thanks,

[StarinieriG]

Hi,

have you tried to add sum before aggr?

sum(aggr(Rangesum( above(
count (
[ID])
,0,RowNo())),Group,Date))

[sunny_talwar · Accepted solution]

May be you need to sort the Date field? Try this

=Aggr(
    RangeSum(Above(
        Count([ID])
    , 0, RowNo()))
, Group, (Date, (NUMERIC)))

[Ceb]

Thanks alot Sunny.

This works. But if I duplicate dates or even if I convert it to Months I have duplicate records on the X-axis

How should I counter that?

[Ceb]

Thanks alot Sunny.

This works. But if I duplicate dates or even if I convert it to Months I have duplicate records on the X-axis

How should I counter that?

[sunny_talwar]

Difficult to know without having to take a look at this

[Ceb]

In terms of a table I am getting something like this

Aug-2019	B	1233
Aug-2019	A	5455
Aug-2019	C	2059
Aug-2019	B	1234
Aug-2019	A	5456
Aug-2019	B	1235
Aug-2019	A	5459
Aug-2019	A	5461
Aug-2019	C	2060
Aug-2019	A	5466
Aug-2019	C	2061
Aug-2019	B	1239
Aug-2019	D	594
Aug-2019	A	5471
Aug-2019	C	2063
Aug-2019	B	1241
Aug-2019	A	5472
Aug-2019	B	1243
Sep-2019	A	5473
Sep-2019	A	5474
Sep-2019	B	1244

whereas I need to get this

Aug-2019	D	594
Aug-2019	C	2063
Aug-2019	A	5472
Aug-2019	B	1243
Sep-2019	A	5474
Sep-2019	B	1244

Hope this helps.

I did try using Month only as a dimension, I got the results I need but when I do Month-Year using this code

date(Date#([Network Eff Date],'MM/DD/YYYY'),'MMM-YYYY'), it duplicates

Also is it something to do with Rangesum and the Aggregate function?

[sunny_talwar]

I would use this for MonthYear

Date(MonthStart(Date#([Network Eff Date], 'MM/DD/YYYY')), 'MMM-YYYY')

[Ceb]

Thanks for the reply.

I tried putting 

Date(MonthStart(Date#([Network Eff Date], 'MM/DD/YYYY')), 'MMM-YYYY')

this in the dimension and in the measure

=Aggr(
RangeSum(Above(
Count([ID])
, 0, RowNo()))
, [Group], ([Network Eff Date], (NUMERIC)))

And I got this result

The dimension looks good but the values messed up. The values of the group went blank .

[sunny_talwar]

I would create this in the script

Date(MonthStart(Date#([Network Eff Date], 'MM/DD/YYYY')), 'MMM-YYYY') as [Network Eff Month and Year]

and then use this

=Aggr(
RangeSum(Above(
Count([ID])
, 0, RowNo()))
, [Group], ([Network Eff Month and Year], (NUMERIC)))