
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
