topic Date Manipulation in QlikView

Date Manipulation

Mon, 19 Sep 2011 18:47:00 GMT

We are relatively new to QV, but we are having what is probably a simple problem to solve, but the solution is eluding us;

We set a variable to indicate the date today;

Let vToday=Date(Now());

So if it were today, it would read "9/19/11"

and we have one to indicate the date a year ago today;

Let vTodayLastYear=MakeDate(vCurrentCalendarYrMinusOne, Month(Now()), Day(Now()));

The date reads "9/19/10"

The problem is I need it to be "9/18/10" and have been having problems getting the syntax right. Any ideas?

Mike

its_anandrjs — Mon, 19 Sep 2011 19:06:00 GMT

Hi Mike,

You need to do some changes in code like

For today date variable it is ok

Let vToday=Date(Now());

It reads 9/20/2011

for second code change Day(Now()) - 1 and the complete code is

Let vTodayLastYear=MakeDate(Year(Now())-1, Month(Now()), Day(Now())-1);

It reads 9/19/2010

Rgds

Anand

swuehl — Mon, 19 Sep 2011 19:12:43 GMT

Hi Mike,

do you want the date 52 weeks back in time?

If today is Monday, 2011-09-19, do you want Monday, 2010-09-20?

Then I would just write

=Date(today()-52*7)

Regards,

Stefan

edit:

Anand, your code is fine for most days, but what about if today is one of the first days of a month?

Mon, 19 Sep 2011 19:19:03 GMT

Hi Mike,

It looks like you want to substract a year to a current date

You can try following expression to substract a year

=addmonths(Date(Now()),-12)

Mon, 19 Sep 2011 19:48:07 GMT

Thanks! This did the trick. We were putting the minus one in the wron paren.

Regards!