Current Month

[#1]

I want to reference the current month in my expression, so that I can sum sales that are current month -1,-2 etc

Can this be done?

[#2]

yes its a date

[martin59]

Hi,

If you want to uses these kind of expressions, i'll try to help you :

// Current Month Sales
Sum({<[Month]={"$(=Month(Today()))"}>} Sales)
// or
Sum({<[Date]={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Sales)
// Last Month Sales
Sum({<[Date]={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Sales)
// Month-2 Sales
Sum({<[Date]={">=$(=MonthStart(Today(),-2)) <=$(=MonthEnd(Today(),-2))"}>} Sales)

Hope that helps you

Martin

[pduplessis]

try putting =month(today()) in a text object and you will get Jul (depending on your month format...) unless DisburseDate is Jul, you need something else..

[#5]

I am trying this but not working, disburse date is sales date. Basically I want to sum sales from 2 months ago based on todays month

sum({<DisburseDate={">=$(=monthstart(today()-2))"},Status = {Active}, Main = {1}, AgreementAgeDays = {'>=45'} >} SaleAmout)

[swuehl]

The quotation marks and commas are always a bit tricky...

sum({<DisburseDate={">=$(=monthstart(today(),-2))"},Status = {'Active'}, Main = {1}, AgreementAgeDays = {">=45"} >} SaleAmout)

Stefan

[swuehl]

me again,

if you want a rolling two month period, use addmonths(today(),-2) instead of monthstart.

monthstart(today(),-2)   --> 01/05/2011

addmonths(today(),-2)  ---> 22/05/2011

Stefan

[#8]

Thanks. This working but can you restrict it to just month -2

[swuehl · Accepted solution]

Sure,

like

sum({<DisburseDate={">=$(=monthstart(today(),-2))<$(=monthstart(today(),-1))"},Status = {'Active'}, Main = {1}, AgreementAgeDays = {">=45"} >} SaleAmout)

Stefan

[#10]

See if this gives you what you're looking for,

=sum(if((num(month(disbursedate))>=num(month(today()))-2 and year(disbursedate) = year(today())),SaleAmount))

It is far from sophisticated (and far from complete), but if you start getting the results you're expecting then we can sort out the details.

[#11]

Thanks. works perfect.