Rolling 1 year from max date

[stha142020]

I want to create a 1 year rolling based on max date like this:

Date	Revenue	Rolling 1 year
01.03.2018	100	100
01.04.2018	100	200
01.05.2018	100	300
01.06.2018	100	400
01.07.2018	100	500
01.08.2018	100	600
01.09.2018	100	700
01.10.2018	100	800
01.11.2018	100	900
01.12.2018	100	1000
01.01.2019	100	1100
01.02.2019	100	1200
01.03.2019	100	1200
01.04.2019	100	1200
01.05.2019	100	1200
01.07.2020	100	100

I have tried the following solution:

Sum(
{< Date={'>$(=Addmonths(max(Date),-11))<=$(=Date(max(Date)))'}>}

Aggr( Rangesum(Above(total Sum({$< >} Revenue),0,12)), Date))

Any suggestions?

Thanks!

[bhavyagrb]

Hello ,

I have tried below with the above provided data ,hope this helps you:

In Backend Script:

data:
load * inline [
Date,Revenue,Rolling 1 year
01.03.2018,100,100
01.04.2018,100,200
01.05.2018,100,300
01.06.2018,100,400
01.07.2018,100,500
01.08.2018,100,600
01.09.2018,100,700
01.10.2018,100,800
01.11.2018,100,900
01.12.2018,100,1000
01.01.2019,100,1100
01.02.2019,100,1200
01.03.2019,100,1200
01.04.2019,100,1200
01.05.2019,100,1200
01.07.2020,100,100
];

load *,
floor(date#(Date,'DD.MM.YYYY')) as DateNum Resident data;

drop table data;

In Frontend expression:

Sum({< DateNum={">=$(=num(addmonths(max(DateNum),-12)))<=$(=num(max(DateNum)))"}>}Aggr( Rangesum(Above(total Sum({$< >} Revenue),0,12)), Date))

Regards,

Bhavya

[Kushal_Chawda]

@stha142020  Bit confusing.  how 01.07.2020  is 100 and not 01.01.2019?

[stha142020]

01.01.2019 have 1100 from 01.03.2018-01.03.2020 (missing date 01.02.2018)
01.07.2020 have only 100 because there isn't any other dates between 01.08.2020-01.07.2020 with revenue