topic Re: Rolling 1 year from max date in App Development

Rolling 1 year from max date

stha142020 — Fri, 18 Sep 2020 10:19:26 GMT

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!

Re: Rolling 1 year from max date

bhavyagrb — Fri, 18 Sep 2020 11:25:49 GMT

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

Re: Rolling 1 year from max date

Kushal_Chawda — Fri, 18 Sep 2020 11:27:07 GMT

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

Re: Rolling 1 year from max date

stha142020 — Fri, 18 Sep 2020 11:43:46 GMT

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