Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
@stha142020 Bit confusing. how 01.07.2020 is 100 and not 01.01.2019?
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