topic New date calculation in a straight table in QlikView

New date calculation in a straight table

Tue, 06 Sep 2016 15:40:37 GMT

Hi,

I need to do a calculation with dates in a straight table. The objective is to calculate a new date combining the following conditions :

If Date <= November of Year N-1 THEN NewDate = September of Year N (is always on Sept).

e.g:

Year N = 2016

If Date = 10-10-2015 Then New Date = 01/09/2016

If Date = 30-10-2015 Then New Date = 01/09/2016

If Date > November of Year N-1 THEN NewDate = September of Year N+1 (is always on Sept).

e.g:

Year N = 2016

If Date = 02-11-2015 Then New Date = 01/09/2017

If Date = 30-12-2015 Then New Date = 01/09/2017

If Date = 02-01-2016 Then New Date = 01/09/2017

This is to be used in a straight table and the New Date is one of the columns of the table.

Could anyone help me to find out a solution for this?

Thanks

Regards

Paulo

Re: New date calculation in a straight table

miguelbraga — Tue, 06 Sep 2016 15:53:05 GMT

This could be done with a simple if function in script like this:

Table:

LOAD *,

if(monthNum > 11, '01/09/' & (year + 1), '01/09/' & year) as [New Date];

LOAD *,

day(Date) as day;

num(month(Date)) as monthNum;

month(Date) as month;

year(Date) as year;

FROM [your connection to your database goes here];

Hope it helps you

Regards,

Re: New date calculation in a straight table

Tue, 06 Sep 2016 16:44:02 GMT

Hi,

The result is correct, but I need to take in consideration the current year. i.e, the new Date must be calculated considering the current date (Year N) as I stated above.

Current date: 06-09-2016

If MyDate <= Nov-2015 (Nov Current Year -1) THEN NewDate = 01/09/2016

If MyDate > Nov-2015 (Nov Current Year -1) THEN NewDate = 01/09/2017

I'm not doing this at script level because I need to change the color of the rows that meet the criteria. So the calculation is being done in the table object directly.

Thanks in advance

Regards

Paulo

Re: New date calculation in a straight table

miguelbraga — Tue, 06 Sep 2016 16:47:31 GMT

Tweak my solution a bit like this:

Table:

LOAD *,

if(monthNum > 11, '01/09/' & (year + 1), '01/09/' & year) as [New Date];

LOAD *,

day(Date) as day;

num(month(Now())) as monthNum;

month(Date) as month;

year(Date) as year;

FROM [your connection to your database goes here];

Re: New date calculation in a straight table

Tue, 06 Sep 2016 17:55:44 GMT

Hi Miguel,

Is not working... For some months the calculation is not correct. e.g. for the date 05-11-2015 gives me the date 01-09-2015. I think the the solution must take in account the month and year. Please check screenshot below.

Could you advise?

Many thanks

Regards

Paulo

Re: New date calculation in a straight table

miguelbraga — Wed, 07 Sep 2016 08:40:26 GMT

I'm sorry, make a little change in script:

Table:

LOAD *,

if(monthNum > 11, '01/09/' & (year + 1), '01/09/' & year) as [New Date];

LOAD *,

day(Now()) as day;

num(month(Now())) as monthNum;

month(Now()) as month;

year(Now()) as year;

FROM [your connection to your database goes here];

Re: New date calculation in a straight table

miguelbraga — Wed, 07 Sep 2016 08:40:48 GMT

This will do you the trick

Re: New date calculation in a straight table

Wed, 07 Sep 2016 09:18:58 GMT

Hi,

Sorry...is not working as expected. With the function now() the date will be always the current date and we need to use the current date (Year) -1.

Probably I'm not explaining well. I think the issue is on the comparison with the month since we are using a fixed value (11) and I think it should be with a variable because we need to refer to the a way

If MyDate <= November Current Year -1 THEN NewDate = 01/09/2016

If MyDate > November Current Year -1 THEN NewDate = 01/09/2017

Thanks

Regards

Paulo

Re: New date calculation in a straight table

miguelbraga — Wed, 07 Sep 2016 09:34:27 GMT

Check my solution:

In the attached file

Regards,

Re: New date calculation in a straight table

miguelbraga — Wed, 07 Sep 2016 09:38:47 GMT

If you want to use the current date then use this solution:

Regards,

Re: New date calculation in a straight table

Wed, 07 Sep 2016 10:22:35 GMT

Hi,

It's working now.

Many thanks

Paulo