topic Variable for current month end in previous year in QlikView

Variable for current month end in previous year

Mon, 17 Nov 2014 17:57:28 GMT

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

Re: Variable for current month end in previous year

marcus_malinow — Mon, 17 Nov 2014 18:02:28 GMT

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.

Re: Variable for current month end in previous year

lironbaram — Mon, 17 Nov 2014 18:09:45 GMT

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