Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try this
Sum({<gSalesMonth={">=$(=AddMonths(Max(gSalesMonth),-13))<=$(=monthend(addmonths(Max(gSalesMonth),-1)))"}>}gSales)
try this
Sum({<gSalesMonth={">=$(=AddMonths(Max(gSalesMonth),-13))<=$(=monthend(addmonths(Max(gSalesMonth),-1)))"}>}gSales)
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)
Hi Kushal,
that works!! I was on right track but could not get the syntax correct
thanks for quick reply
regards Peter
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
Please mark the Correct and helpful answers to close the thread
hi why dont i see correct answer button??
Might be a browser issue. Try login in with a different browser.
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