Rolling Sum with two years compared

[dnl_kln]

Hello All,

I've searched a lot to solve my Problem.

In Qlik I compare two years of Data, showing Offers as monthly values.

I would like to show a rolling sum of the last 12 month, base on the monthly row, so that (see attached image) the january value in the Column "Angebote rollierend" shows the sum of january and the last 11 month of the previous year ,....., November shows the last 11 Months of 2021 and Dezember auf 2020.

Is there any way to achieve this beside building absolutely complex set analysis formulas? My data model does have the autocalendar-fields, e.g. "MonthsAgo".

Thanks in Advance for your help!

Daniel

[rubenmarin]

Hi, maybe you can use a solution like the AsOf calendar: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Also note that by deafult QlikSense helps you to add 12 (or x) steps accumulation in the expression properties:

[dnl_kln]

Hello Rubenmarin,

thank your for your help! I added the "As-of-table". My Formula looks like this:

Sum({$<MonthDiff={"<12"},YearDiff={"0"}>} [Angebote.Angebotssumme])

Please notice, that I don't want to accumulate the values but build a single 12-month sum for every row (for every month, I want the sum of the last 12 month). Right now, it's just adding the previous years month value with the value of the month in actual year.

Maybe you can find the mistake in my formula.

[rubenmarin]

Hi,only with "Sum({$<MonthDiff={"<12"}>} [Angebote.Angebotssumme])" it should work, note that to make this solution to work you need to use the AsOfMonth as dimension.

[dnl_kln]

Thanks again for your reply!

When using AsofMonth as the Months Dimension in the Table, the effect of having two comparing columns for year and previous year is gone...

[rubenmarin]

Hi, yes, it would need some adjusments to look as before, and I will need a sample to work with it, can you upload an example with some dummy data?

But first... I see that it looks like each year starts from 0, so jan is only jan, feb is jan+feb, etc... it's like that? In that case, and if the table starts always on Jan you don't really need an AsOf table, just an accumulation by rows, so each row sums the rows above it

RangeSum(Above([ColumnExpression],0,RowNo(TOTAL)))

[dnl_kln]

The accumulation is a little different then you guess:

We build for every month the sum of the last 12 months, so for April 2021 it is:

April 2021

March 2021

February 2021

January 2021

Dezember 2020

...

May 2020

For November 2021 it is:

November 2021

October 2021

September 2021

...

Dezember 2020

I attach the App with some sample Data!

[rubenmarin]

Hi, well I can't work with that data because I can't reload it. Anyway, you can try just adding month field in the asof calendar using Month(AsOfMonth) as MonthDim and you probably need to adjust the expression to use this for current and last year.

I usually work in a different way, if there are not many selections I have a table with values accumulated by month, and for each accumulated key I have the 12M value precalculated

LOAD
		*,
		RangeSum(MonthAmount, MonthAmount2, MonthAmount3, MonthAmount4, MonthAmount5, MonthAmount6, MonthAmount7, MonthAmount8, MonthAmount9, MonthAmount10, MonthAmount11, MonthAmount12) as Amount12M,
;
LOAD
  KeyAccum, // Ir could have a combined key with year, month, company,...
  Company,
  MonthAmount,
  If(Company=Peek(Company), Peek('MonthAmount'), 0) as MonthAmount2,
  If(Company=Peek(Company), Peek('MonthAmount2'), 0) as MonthAmount3, 
  //Add months until 12																				
Resident
  tmpMonthlyAmounts
Order By
  Company, Year, Month
;

 I think the AsOf table could be easier, I just avoid it to use the 'normal' month field as dimension