Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
salonibhatia
New 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 

1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: Rangesum aggregation based on year

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"

4 Replies
Frank_Hartmann
Honored Contributor II

Re: Rangesum aggregation based on year

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

Re: Rangesum aggregation based on year

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

Re: Rangesum aggregation based on year

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

Re: Rangesum aggregation based on year

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