New date calculation in a straight table

[#1]

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

[miguelbraga]

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,

MB

[#3]

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

[miguelbraga]

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];

[#5]

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

[miguelbraga]

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];

[miguelbraga]

This will do you the trick

[#8]

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

[miguelbraga]

Check my solution:

In the attached file

Regards,

MB

[miguelbraga · Accepted solution]

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

Regards,

MB