Skip to Content
avatar image
Former Member

Using Previous(Self) function on DateTime column

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 20, 2017 at 02:49 PM

    have you tried like this.

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

    Date is your date object

    Add comment
    10|10000 characters needed characters exceeded

    • 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)

  • avatar image
    Former Member
    Nov 20, 2017 at 03:06 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 20, 2017 at 03:35 PM

    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"))))

    Add comment
    10|10000 characters needed characters exceeded

    • 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!