Month to Date Set Analysis

[#1]

Hi guys!

I need your help regarding on Month to Date using set analysis. I have this code for my MonthToDate computation:

sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE)))) <=$(=Max(PAID_DATE))"}, Year=, Month=, Day=>} PAIDAMOUNT)

My question is, What will be my set analysis for the following :

1.) Previous Month with the same date (example: 11/15/2010 selection should compute = from 10/01/2010 to 10/15/2010)

2.) Full Month Two Months Ago (example: 11/15/2010 selection should compute = from 09/01/2010 to 09/30/2010)

3.) Full Month Three Months Ago (example: 11/15/2010 selection should compute = from 08/01/2010 to 08/31/2010)

Or If you have any suggestion or work around or easier approach regarding my problem, it will be very much appreciated.

Thanks in advance!

Regards,

Lester

[pover · Accepted solution]

Lester,

Try the following for each option:

1)sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE),-1))) <=$(=addmonths(Max(PAID_DATE),-1))"}, Year=, Month=, Day=>} PAIDAMOUNT)
//most month functions have a month offset parameter you can use and if not use addmonths()

2)sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE),-2))) <=$(=date(MonthEnd(Max(PAID_DATE),-2)))"}, Year=, Month=, Day=>} PAIDAMOUNT)

3)sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE),-3))) <=$(=date(MonthEnd(Max(PAID_DATE),-3)))"}, Year=, Month=, Day=>} PAIDAMOUNT)

Regards.

[#3]

Thank you very much Karl!!! Those 3 set analysis works..

Regards,
Lester

[#4]

Hi Karl,

I have a follow-up question:

What will be my Set Analysis for Yesterday using this Month To Date Set Analysis?

sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE)))) <=$(=Max(PAID_DATE))"}, Year=, Month=, Day=>} PAIDAMOUNT)

Thanks in advance!

Lester

[pover]

Try this...

sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE)))) <=$(=today()-1)"}, Year=, Month=, Day=>} PAIDAMOUNT)

Regards.