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

Variable for current month end in previous year

Hi,

I have tried to create a variable to show the date for the end of the current month in the previous year to do some YOY YTD analysis.

I have used the variable:

date(YearEnd(today(),-1,(month(today())+1)))

It appears to work i.e. my result is currently 30/11/2013. However, it doesn't seem to  to work in my expression, which is designed to pick up all data for 2013 from Jan to Nov. Instead I get Jan 2013 up to my latest data in 2014.

My expression is:

SUM({$<[Type]  = {'Actual*'}, [applieddate_ReportDate] =  {">=$(vDateYearStartLess1)"},[Date] = {"<=$(vDateCurrentMonthEndYearLess1)"}>}Value)/1000

Does anyone have any tips? I am new to Qlikview so any help would be much appreciated!

Thanks

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

first you need to make sure

the format of the variable is the same as the field

second you used one date field to use the start of last year and another field to determine the end of last year

usually you use the same field in your case may be something like

SUM({$<[Type]  = {'Actual*'}, [applieddate_ReportDate] =  {">=$(vDateYearStartLess1)  <=$(vDateCurrentMonthEndYearLess1) "}>}Value)/1000


third there is a much simpler function to find previous year end of the month

you can use monthend(addmonths(today(),-12))

View solution in original post

2 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi Leanne,

I'm not sure which of your dates you're using to filter here - applieddate_ReportDae or Date. However the principle is the same regardless.

Your criteria should be something like

Date={">=$(vDateYearStartLess1)<=YearEnd<=$(vDateCurrentMonthEndYearLess1)"}

Both the >= and <= should be in the same search string.

lironbaram
Partner - Master III
Partner - Master III

first you need to make sure

the format of the variable is the same as the field

second you used one date field to use the start of last year and another field to determine the end of last year

usually you use the same field in your case may be something like

SUM({$<[Type]  = {'Actual*'}, [applieddate_ReportDate] =  {">=$(vDateYearStartLess1)  <=$(vDateCurrentMonthEndYearLess1) "}>}Value)/1000


third there is a much simpler function to find previous year end of the month

you can use monthend(addmonths(today(),-12))