Rolling 13 Months to Month End = Previous Month

[Peter_Brunner]

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

[Kushal_Chawda · Accepted solution]

try this

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

[sunny_talwar]

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)

[Peter_Brunner]

Hi Kushal,

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

thanks for quick reply

regards Peter

[Peter_Brunner]

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

[Kushal_Chawda]

Please mark the Correct and helpful answers to close the thread

[Peter_Brunner]

‌hi why dont i see correct answer button??

[oknotsen]

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

[Peter_Brunner]

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