Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor III

Rangesum aggregation based on year

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

Labels (6)

• rangesum

1 Solution

Accepted Solutions
Master II

script:

``````Table:
LOAD Date#(monthyear, 'MMM-YY') as Month, sum;
[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:
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"

4 Replies
Master II

script:

``````Table:
LOAD Date#(monthyear, 'MMM-YY') as Month, sum;
[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:
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"

Contributor III
Author

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

Contributor III
Author

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

Contributor III
Author

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

Community Browser