# App Development

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for
Did you mean:
Contributor

## Rolling 1 year from max date

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(

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

Any suggestions?

Thanks!

3 Replies
Partner - Contributor II

Hello ,

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

In Backend Script:

data:
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
];

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?

Contributor
Author

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

Tags
Community Browser