How to get previous Month data

[#1]

Hi All,

in my report I have calendar objects for FromDate and ToDate. when I select a date range it is giving the results.

here my issue is if I select a date range in a particular month, will I get both selected month and previous month data in the same report(Line Chart).

Example: when I select Date range 01-October-2015 to 31-Oct-2015. it will give the total volume for the October Month, I want to get Previous month September total volume.

Dimension: MonthYear(Jan-2015)

Expression: Sum(Amount)

Please help me,

Thanks.

[Chanty4u]

hi,

Current-mnth

=sum({<Date={'$(=Date(Max(Date),'M/D/YYYY'))'}>}Balance)

Previous:mnth

=sum({<Date={"$(=Date(Max({1}{<Date={'<$(=Date(Max(Date),'M/D/YYYY'))'}>}Date),'M/D/YYYY'))"}>}Balance)

hope this helps u

thanks

Suresh

[sunny_talwar]

May be this for previous month:

=If(Sum(Amount) > 0, Above(Sum({<Date, MonthYear>}Amount)))

[PrashantSangle]

Hi,

try

sum({<Date={">=Date(addmonths(Monthstart(max(Date),-1)))<=Date(addmonths(max(Date),-1))"}>}Amount)

Regards

[Kushal_Chawda]

For previous month

=sum({<Date={">=$(=Date(monthstart(addmonths(max(Date),-1))))<=$(=Date(monthend(addmonths(max(Date),-1))))"}, Year=,Month=, Date=>}Amount)

[Anonymous]

Use MonthStart() and MonthEnd() as suggested by Max/Kush

[sunny_talwar]

Is this what you want?

Use this dummy script:

Table:

LOAD Date(MakeDate(2015,1,1) + RecNo() - 1) as Date,

  MonthName(MakeDate(2015,1,1) + RecNo() - 1) as MonthYear,

  Ceil(Rand() * 100000) as Value

AutoGenerate (Today() + 1 - MakeDate(2015,1,1));

[effinty2112]

Hi,

If you have a dimension called MonthYear then I'm guessing you've got a master calendar. If you add a MonthID field to the calendar you'll make life easy. If your Calendar is loaded in the usual way, creating dates from the earliest to the latest, then if you add something like this above your master calendar script:

LOAD

*,

AutoNumber(MonthYear , 'MonthID') as MonthID

;

LOAD ...

You'll have a nice integer field you can do simple arithmetic on. Now if you select a month an expression with the set modifier <MonthID = {$(=Max(MonthID)-1)}, MonthYear =>} will give the calculation for the previous month. You may need to add other terms to this, such as ... Month =, Year =... so that it will disregard other selections as may be required. Then to tidy it all up you can create a variable to hold your set modifier and if you want add a parameter for the number of months you want to look back instead of having 1 hard written then you have a very flexible and easy to read and maintain solution

Cheers

Andrew