Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
salonibhatia
Contributor III
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)
1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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"

View solution in original post

4 Replies
Frank_Hartmann
Master II
Master II

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
Contributor III
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

salonibhatia
Contributor III
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

salonibhatia
Contributor III
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)