on 11-20-2017 2:31 PM
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
=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
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"))))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
have you tried like this.
=ToDate(Previous([Date]);"MM/dd/yy HH:mm")
Date is your date object
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.