Select last month and then three months after that in variabes

[Anonymous]

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

[MK_QSL]

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)

[jonathandienst]

>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.

[Anonymous]

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

[reddy-s]

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

[Anonymous]

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

[Anonymous]

Hi,

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

Thanks

Sangram

[jeroen_wagner]

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: 

LOAD

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

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

AUTOGENERATE 1 

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

Calendar: 

LOAD

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; 

[reddy-s]

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

[Anonymous]

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

TempCalendar:

Load

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

Autogenerate

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