topic Rangesum aggregation based on year in QlikView

Rangesum aggregation based on year

salonibhatia — Sat, 16 Nov 2024 03:08:34 GMT

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

Re: Rangesum aggregation based on year

Frank_Hartmann — Sat, 29 Jun 2019 16:32:24 GMT

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"

Re: Rangesum aggregation based on year

salonibhatia — Sat, 29 Jun 2019 17:24:00 GMT

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

Re: Rangesum aggregation based on year

salonibhatia — Sat, 29 Jun 2019 18:27:35 GMT

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

Re: Rangesum aggregation based on year

salonibhatia — Sat, 29 Jun 2019 18:41:49 GMT

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