Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
MK_QSL
MVP
MVP

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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

reddy-s
Master II
Master II

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
Not applicable
Author

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
Not applicable
Author

Hi,

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

Thanks

Sangram

jeroen_wagner
Partner - Creator
Partner - Creator

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
Master II
Master II

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
Not applicable
Author

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;