Qlik Community

Ask a Question

App Development

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

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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