Rolling Expression

[rammuthiah]

Can anyone help me to figure it out the concept of Rolling 6 months. I have 2 years data. And I have to show from the month of today to 6 months data in Bar chart. I tried whatever had given in qlik community without using Master Calendar.

Dimension :  have to show by month

Measure : ????

Date , Amount

01-01-2015 , 100

01-02-2015 , 120

01-03-2015 , 140

01-04-2015 , 160

01-05-2015 , 180

01-06-2015 , 200

01-07-2015 , 220

01-08-2015 , 240

01-09-2015 , 260

01-10-2015 , 280

01-11-2015 , 300

01-12-2015 , 320

01-01-2016 , 340

01-02-2016 , 360

01-03-2016 , 380

01-04-2016 , 400

01-05-2016 , 420

01-06-2016 , 440

01-07-2016 , 460

01-08-2016 , 480

01-09-2016 , 500

01-10-2016 , 520

01-11-2016 , 540

01-12-2016 , 560

01-01-2017 , 580

01-02-2017 , 600

01-03-2017 , 620

01-04-2017 , 640

01-05-2017 , 180

01-06-2017 , 200

01-07-2017 , 220

01-08-2017 , 240

01-09-2017 , 260

01-10-2017 , 280

01-11-2017 , 300

01-12-2017 , 320

01-01-2018 , 340

01-02-2018, 360

01-03-2018 , 380

01-04-2018 , 400

01-05-2018 , 420

01-06-2018 , 440

01-07-2018 , 460

01-08-2018 , 240

01-09-2018 , 260

01-10-2018 , 280

01-11-2018 , 300

01-12-2018 , 320

01-01-2019 , 340

01-02-2019, 360

01-03-2019 , 380

01-04-2019 , 400

01-05-2019 , 420

];

I want to display from Today's month (i.e from Nov 2017 ) to April 2018 in front end.

Result :

Month          Sum

Nov 2017    blabla..

Dec 2017    blabla...

Jan 2018     blabla...

Feb 2018    blabla...

Mar 2018    blabla...

Apr 2018     blabla...

[agigliotti]

you can use the below expression as measure:

=sum( {< Date = {">=$(=MonthStart(Today()))<=$(=MonthEnd(AddMonths(Today(), 6)))"} >} Amount )

[prat1507 · Accepted solution]

Hi

PFA the desired app.

Regards
Pratyush

[rammuthiah]

And if I need from this by week onwards (i.e Nov week no. 45.)?

[sunny_talwar]

Best way to handle this would be to use The As-Of Table

[rammuthiah]

It works. Thanks

[lzanetti]

Hi Andrea,

I tryed your solution to solve my problem in

Rolling sum on Qlik Sense

editing it in this way:

=sum( {< Date = {>=$(=MonthStart([Posting.Date.autoCalendar.MonthsAgo] = 12))} <=$(=MonthEnd([Posting.Date.autoCalendar.YearMonth])))} >} Amount )

(because that's exactly what I need to do: plot, month by month, the sum of Quantity over latest 12 months)

but it doesn't work.

Could you please give me a help?

[agigliotti]

Hi Luigi,

For your request you should use below expression:

=sum( {< Date = {">=$(=MonthStart(AddMonths(Today(), -12)))<=$(=MonthEnd(AddMonths(Today(), -1)))"} >} Amount )

what above for the time period: nov '16 - oct '17