Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Thanks for your help guys

  

Sales YearSales Month
20151
20152
20153
20154
20155
20156
20157
20158
20159
201510
201511
201512
20161
10 Replies

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

lakwinder
Contributor III

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

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

reddys310
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

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

lakwinder
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

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

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; 

reddys310
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

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

Load

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

Autogenerate

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