topic Re: Define variables in QlikView

Define variables

Mon, 02 Mar 2015 22:03:41 GMT

Hi I need to define dynamic variable based on today's date. need assistance when it comes to year & month logic.

gives me current day.

Let vCurrentDay = Day(today());

gives me current month in words & in numbers.

LET vCurrentMonth

= Month(Today());

Gives me current year:

Let vCurrentYear = Year(Today());

I need help defining following.

Give me Month start variable that will be 3 months prior to current month regardless of what date we fall into current month.

So if we are in Mar it should return Jan. Basically it is going back entire 3 months not 3 months from today.

Give me Year variable that will fall into whatever MonthStart (point#1) falls into.

thanks

Re: Define variables

maxgro — Mon, 02 Mar 2015 22:17:29 GMT

AddMonths(MonthStart(Today()),-2)

Year(AddMonths(MonthStart(Today()),-2))

Re: Define variables

Mon, 02 Mar 2015 22:30:38 GMT

Can you please explain how formula works.

Re: Define variables

maxgro — Mon, 02 Mar 2015 22:41:23 GMT

monthstart(Today()) is the start month of today

addmonths(........, -2) is the start month of today minus 2 months

my date is in DD/MM/YYYY format

Re: Define variables

manojkulkarni — Tue, 03 Mar 2015 01:00:14 GMT

MonthStart ()

Returns a value corresponding to a timestamp with the first millisecond of the first date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

monthstart ( '2001-10-19' ) returns '2001-10-01' with an underlying numeric value corresponding to '2001-10-01 00:00:00.000'

monthstart ( '2001-10-19', -1 ) returns '2001-09-01' with an underlying numeric value corresponding to '2001-09-01 00:00:00.000'

AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'