0

# Using Previous(Self) function on DateTime column

Nov 20, 2017 at 02:31 PM

39

Former Member

I am working on a data set that essentially looks like this:

I am trying to get this last column to populate.

Essentially, I am using the following formula:

=If(LineNumber()=2) Then RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover])

Else RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover]+(DaysBetween(CurrentDate();ToDate(Previous(Self);"MM/dd/yy HH:mm:ss"))))

But it is not working at all, yet.

This formula is explained as follows:

If(LineNumber()=2) Then RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover])

The header row is line 1, so the first detail line is row 2 (per SAP documentation and testing verified this).

If the line number equals 2, calculate the relative data as the sum of adding the production days + setup days to the current date, this value is CORRECT on my report

All other lines of the report are wrong though.

The Else statement is trying to do the following:

Find the relative date by adding days to the current date where the days value is

• [Prod Time (Days)]+[time_changeover]+
• (DaysBetween(
• CurrentDate();
• ToDate(Previous(Self);"MM/dd/yyyy HH:mm:ss")) ß this is where the issue lies
• o)
• )
```=If(LineNumber()=2) Then RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover])
Else RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover]+(DaysBetween(CurrentDate();ToDate(Previous(Self);"MM/dd/yyyy HH:mm:ss"))))```

I believe the issue lies with the statement ToDate(Previous(Self);"MM/dd/yy HH:mm:ss"))

In order to get the value from the previous line, I need to use the Previous(Self) function. But, this will NOT return the value as a date (even though the [relative_date] variable is defined as a Date time element). I need to convert it to a date using the ToDate() function. This appears to be the issue, of which I have tried multiple variations and cannot get it to work.

Here is the actual output I am getting:

Any help is appreciated

AMIT KUMAR
Nov 20, 2017 at 02:49 PM
0

have you tried like this.

=ToDate(Previous([Date]);"MM/dd/yy HH:mm")

Show 1 Share
Former Member

Get the following error:

The formula for variable [relative Date] contains a reference to a variable with the same short name. (IES 10040).

This is why I MUST use Previous(Self)

Former Member Nov 20, 2017 at 03:06 PM
0

I have tried multiple variations of this, all result in the #ERROR.

FYI, by NOT trying to get previous date, the variable returns the a correct date/time element but offset by current date only. I just cannot get the previous date into the variable:

```=If(LineNumber()=2) Then FormatDate(RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover]);"MM/dd/yyyy HH:mm")
Else FormatDate( RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover]);"MM/dd/yyyy HH:mm")
```

This is the result of doing the RelativeDate referencing ONLY current Date and adding in the Production Days + setup offset:

Previous(Self) is the issue.

dt2.png (38.0 kB)
Show 1 Share

are you getting values in the column if you dragged =Previous(Self) only?

AMIT KUMAR
Nov 20, 2017 at 03:35 PM
0

not very sure if i understood correctly.you wan to display the value via previous self from line number 2 calculation. highlighted one i have added.

=If(LineNumber()=2) Then RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover]) Else RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover]+(DaysBetween(CurrentDate();ToDate(Previous(If(LineNumber()=2) Then RelativeDate(CurrentDate();[Prod Time (Days)]+[time_changeover]));"MM/dd/yyyy HH:mm:ss"))))

Show 1 Share
Former Member

Let's not over complicate this: Essentially, need to add the production days plus setup offset to previous date.

But I CANNOT get the previous date!