Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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.
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))