10 Replies Latest reply: Jan 27, 2016 2:33 PM by Sangram Reddy

# Select last month and then three months after that in variabes

Hi Guys,

My monthly reports report on previous month, so i need a variable to pick up the last month and then combined last three months.  My fields for Year and Month are set up like below.  So for current report i'll need Sales Month 12,11,10 and Sales year 2015.

Thanks for your help guys

 Sales Year Sales Month 2015 1 2015 2 2015 3 2015 4 2015 5 2015 6 2015 7 2015 8 2015 9 2015 10 2015 11 2015 12 2016 1
• ###### Re: Select last month and then three months after that in variabes

Create YearMonthID in your script as below.

AutoNumber([Sales Month]&[Sales Year]) as YearMonthID

Now you can use as below to get the Sales for Last 3 months excluding current month dynamically.

=SUM({<YearMonthID = {">=\$(=Max(YearMonthID)-3)<=\$(=Max(YearMonthID)-1)"}>}Sales)

• ###### Re: Select last month and then three months after that in variabes

>Create YearMonthID in your script as below.

>AutoNumber([Sales Month]&[Sales Year]) as YearMonthID

...And make sure that the load is sorted by Year and Month to keep the YearMonthIDs in chronological order.

• ###### Re: Select last month and then three months after that in variabes

Probably a silly question, but how do i Sort it by Year and Month in chronological order as the numbering is allover the place.

• ###### Re: Select last month and then three months after that in variabes

Cheers guys, I'll give it a go after lunch.

• ###### Re: Select last month and then three months after that in variabes

Hi Lak,

Manish's reply should hep you solve the issue.

If you are looking for last quarter information (instead of last 3 months) then go with this:

You can easily achieve this by creating an extra field in the master calendar  below:

InQuarter(TempDate,\$(vToday),-1)*(-1) as [LastQ], // for whole of last quarter

InQuarterToDate(TempDate,\$(vToday),-1)*(-1) as [LastQTD], // for last quarter to date

Now use this as your expression:

SUM({< LastQ = {1} >}Sales)

or

SUM({< LastQTD = {1} >}Sales)

This will solve your issue and will always work dynamically.

This will improve your visualizations performance as well. Hope this helps!

Thanks,

Sangram

• ###### Re: Select last month and then three months after that in variabes

Hi,

Is TempDate  the date field in my load? I only have years and months as 1 to 12.

Thanks

Sangram

• ###### Re: Select last month and then three months after that in variabes

Hi Lak,

Tempe date is the date to which you are referring to for example:

InQuarter(date#('2015/10/10','YYYY-MM-DD'),\$(vToday),-1)*(-1) as [LastQ], // for whole of last quarter

would return a flag value as 1. So temp date is basically the date you are trying to evaluate and vToday is todays date.

Its always better to have a master calendar to do such calculations.

Thanks,

Sangram

• ###### Re: Select last month and then three months after that in variabes

Use this MasterCalender below

and use MonthFlag1 to go back one month.

//------------------------------------------------------------------------------------------

LET vDateMin = Num(PEEK('OrderDate' , 0, 'Orders'));

LET vDateMax = Num(PEEK('OrderDate' , -1, 'Orders'));

LET vDateToday = Num('28-03-2009');

TempCalendar:

\$(vDateMin) + RowNo() - 1 AS DateNumber,

Date(\$(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE \$(vDateMin)+IterNo()-1<= \$(vDateMax);

Calendar:

Date(TempDate) AS OrderDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays,

//Fiscal Years

date(yearstart(TempDate,0,4),'YYYY') as "Fiscal Year",

// Flagfunctions

InYearToDate(TempDate,\$(vDateToday), 0)*-1 As CurYTDFlag,

InYearToDate(TempDate,\$(vDateToday), -1)*-1 As LastYTDFlag,

InYear(TempDate,\$(vDateToday), 0)*-1 AS CurYearFlag,

InYear(TempDate,\$(vDateToday), -1)*-1 AS LastYearFlag,

InMonth(TempDate,\$(vDateToday),0)*-1 AS MonthFlag,

InMonth(TempDate,\$(vDateToday),-1)*-1 AS MonthFlag1,

InMonth(TempDate,\$(vDateToday),-2)*-1 AS MonthFlag2

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

• ###### Re: Select last month and then three months after that in variabes

I tried that code but i keep getting a error near the – 1)

TempCalendar:

date(\$(varMinDate) + rowno() – 1) As TempDate,

Autogenerate

\$(varMaxDate) – \$(varMinDate) + 1;

• ###### Re: Select last month and then three months after that in variabes

Hi Lak,

I can see that you are trying to create a master calendar:

Follow this and this should solve your issue:

Creating A Master Calendar

Thanks,

Sangram.