Rangesum aggregation based on year

[salonibhatia]

Hi,

I need below output 

monthyear	sum	cumulative
Jun-18	651.62	651.62
Jul-18	675.53	1,327.15
Aug-18	362.19	1,689.34
Sep-18	336.70	2,026.03
Oct-18	460.60	2,486.64
Nov-18	450.74	2,937.37
Dec-18	533.93	3,471.31
Jan-19	1,410.87	1,410.87
Feb-19	245.62	1,656.49
Mar-19	364.46	2,020.96
Apr-19	490.58	2,511.54
May-19	325.19	2,836.73

Explanation: when a new year starts I need  aggregation based on year...can't add hidden dimensions as I need to create a line chart 

[Frank_Hartmann · Accepted solution]

script:

Table:
LOAD Date#(monthyear, 'MMM-YY') as Month, sum;
LOAD * Inline
[monthyear, sum
Jun-18,651.62
Jul-18,675.53
Aug-18,362.19
Sep-18,336.70
Oct-18,460.60
Nov-18,450.74
Dec-18,533.93
Jan-19,1410.87
Feb-19,245.62
Mar-19,364.46
Apr-19,490.58
May-19,325.19
];

FinalTable:
LOAD *,
	 Month as MonthNew,
	 Year(Month) as Year
Resident Table
Order By Month;

DROP Table Table;

Straight Table:

Dim:

MonthNew

Expression:

Aggr(RangeSum(Above(Sum(sum), 0, RowNo())), Year, MonthNew)

and make sure that Field MonthNew is sorted by "Load Order"

[salonibhatia]

Thanks @Frank_Hartmann ..that worked...my bad I was using total ...thanks again for your help...and yes have sorted the monthnew in script and created row number too to restrict it to top 12 in graph

[salonibhatia]

Sorry but just realized one more change to this....say Jun-18 comes up in the top 12 months which will show as the first month in the table but the sum should be of all the months less than June and so on should the rangesum work

[salonibhatia]

@Frank_Hartmann ...I could do that by using the below expression but if there is a better way please guide 🙂 

if(RowNo()=1,SUM(total{<sort= {"<=$(vSort_top_12)"}>}sum,Aggr(RangeSum(Above(Sum(sum), 0, RowNo())), year,Monthnew) +SUM({<sort= {"<=$(vSort_top_12)"}>}sum))

where vSort_top_12==max(sort)-11

  sort= rowno() given in backend with acs sorting 

I had to use calculated dimension to get top 12 as exp uses less than that...

Dimension-

=if(sort>=$(vSort_top_12),Monthnew)