# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor III

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

 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
10 Replies
MVP

## 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)

MVP

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

Contributor III

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

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

Honored Contributor II

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

Contributor III

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

Contributor III

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

Contributor

## 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;

Honored Contributor II

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

Contributor III

## 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: