Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Rolling 13 Months to Month End = Previous Month

Hi All,

I have a question for set analysis to show Rolling 13 Months data to Previous Month End

currently I have Sum({<gSalesMonth={">=$(=AddMonths(Max(gSalesMonth),-13))"}>}gSales)

have tried many different ideas but no joy!

I have attached a sample

any help appreciated

thanks Peter

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

try this

Sum({<gSalesMonth={">=$(=AddMonths(Max(gSalesMonth),-13))<=$(=monthend(addmonths(Max(gSalesMonth),-1)))"}>}gSales)

View solution in original post

8 Replies
Highlighted
MVP
MVP

try this

Sum({<gSalesMonth={">=$(=AddMonths(Max(gSalesMonth),-13))<=$(=monthend(addmonths(Max(gSalesMonth),-1)))"}>}gSales)

View solution in original post

Highlighted

Script changes:

Months:

LOAD Date(Date#(gSalesMonth, 'MMM YYYY'), 'MMM YYYY') as gSalesMonth,

  gSales,

  gSalesMargin,

  Date;

load *  inline [

gSalesMonth, gSales, gSalesMargin,Date

May 2015,500,200

Jun 2015,1000,400

Jul 2015,300,100

Aug 2015,500,100

Sep 2015,400,200

Oct 2015,400,200

Nov 2015,500,101

Dec 2015,600,400

Jan 2016,200,100

Feb 2016,400,100

Mar 2016,500,100

Apr 2016,450,230

May 2016,550,330

Jun 2016,20,10

];

Expression:

Sum({<gSalesMonth = {"$(='>=' & Date(MonthStart(Today(), -13), 'MMM YYYY') & '<=' & Date(MonthEnd(Today(), -1), 'MMM YYYY'))"}>}gSales)

Highlighted
Creator
Creator

Hi Kushal,

that works!!  I was on right track but could not get the syntax correct

thanks for quick reply


regards Peter

Highlighted
Creator
Creator

Hi Sunny,

this also works I like the MonthStart and MonthEnd functions

still getting my head around syntax  can you explain why this is written like this '>='  ?

thanks for your help

regards Peter

MVP
MVP

Please mark the Correct and helpful answers to close the thread

Highlighted
Creator
Creator

‌hi why dont i see correct answer button??

Highlighted
Master III
Master III

Might be a browser issue. Try login in with a different browser.

Don't allow Perfection to be the enemy of Good Enough.
May you live in interesting times!
Highlighted
Creator
Creator

Hi Onno,

I found it when you are in the inbox you can only mark as Helpful and the replies have Gray background....to see the Correct answer button you have to click on discussion heading which links to "Original White background page" and the buttons appear there. Still Learning !!

thanks for you help

regards Peter