Monthly Accumulated YoY (based on endorsement date)

[MedAt]

Hello, I am fairly new on Qlik Sense and trying to make my way around it.

I am trying to represent in a visual way (Area Chart) the monthly accumulated income (comparative to previous year). the below table shows the monthly sales and cumulated one... I need to show it in a line chart (seperating the years as a comparison)

the function I used for the cumulated one in the table is:

rangesum(above(YearStart(Sum(Sales)),0,RowNo ()))

but when I copy it onto the line chart, it cumulated the first month of the greatest year (this case 2020) with the first month of last one... as shown in the second screen.

I use the dimension : EndoDate as a time indicator, and I need it brought by month and then seperated by year. of course, I can then select the years I want to compare (2, 3 or more)

any help please? Thanks in advance

[fosuzuki]

Which Qlik Sense version are you using? Qlik "recently"(not sure when) added an accumulation feature for the expressions in a Line Chart.

The idea is that you can put your base expression like Sum(Sales), and right under it there is an option called Modifier. Choose accumulation, choose the month dimension and full range. This should do the trick.

* on the background, it generates the RangeSum expression. This is what I get for a similar chart:

Aggr(RangeSum(Above( (　Sum(Sales)　) , 0, RowNo())), ([$(=Replace(GetObjectField(1),']',']]'))], (Numeric, Ascending), (Text, Ascending)), ([$(=Replace(GetObjectField(0),']',']]'))], (Numeric, Ascending), (Text, Ascending)))

[MedAt]

Thanks a lot, but it seems it is cumulating the entire history and not only the selected years.

Is there a way to just stop the cumulation to only the selected years in the field selector?

I am using the same fields (endo date) as date, but adding year function to it in the field drop down : 

=year([Endo date])

Thanks again

PS: I am not sure of the version but I don't have the modifier option.

[fosuzuki]

I tried creating the accumulation without the Modifier. This should work:

=Aggr(
RangeSum(Above(Sum(Sales), 0, RowNo())),
([=year([Endo date]])], (Numeric, Ascending), (Text, Ascending)),
([=month([Endo date]])], (Numeric, Ascending), (Text, Ascending)))

month([Endo date]]) should be the Area dimension

year([Endo date]]) should be the Group dimension

[MedAt]

Thanks a lot, still showing a cumulated over the years and not only displaying the ones of the selected years.

really appreciate the help.

Regards

[fosuzuki]

If you don't have set analysis in the expression, the chart should respect the selection.

Are you using Alternate States?

[MedAt]

I am just using a set of filters with no alternate states.

Sorry if I am not getting it completely, the dimensions are a year and month function of a date, and I need the measure to be the sales by month cumulated by year

the fields: [endo date] and [sales], the latter is divided by a reference number for currency conversion purpose.