Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
stha142020
Contributor
Contributor

Rolling 1 year from max date

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

DateRevenueRolling 1 year
01.03.2018100100
01.04.2018100200
01.05.2018100300
01.06.2018100400
01.07.2018100500
01.08.2018100600
01.09.2018100700
01.10.2018100800
01.11.2018100900
01.12.20181001000
01.01.20191001100
01.02.20191001200
01.03.20191001200
01.04.20191001200
01.05.20191001200
01.07.2020100100

 

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!

3 Replies
bhavyagrb
Partner
Partner

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
Contributor
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