get the previous row value -- Need help

Chandler_Bing

Hello there,

i'm working with a dataset which involves choosing the values dynamically.

Example:

Fiscal year Current date

2018-08-01 2018-10-01

2018-09-01 2018-10-01

2018-10-01 2018-10-01

2018-11-01 2018-10-01

2018-12-01 2018-10-01

Now when ever there is a match of the fiscal year with the current date I need to get the values from the previous row of the match till the end.

In the above example the result i'm trying to get is :

Fiscal year:

2018-09-01

2018-10-01

2018-11-01

2018-12-01.

Can anyone help me with this please. I think i need to use the multi row formula tool. But not sure about the formula.

Thanks

Workflow

Date Time

Dynamic Processing

Time Series

Accepted answers

danrh

I'm not able to run your workflow, but just looking at it the issue looks to be that the Fiscal Year periods are all on the first and the Current Date is the current date (go figure...) so they'll only be equal on the first of each month.

You can either change the Current Date to the first of the month, or you can alter the formula to something like:

IF !IsNull([Row-1:Result]) THEN [Fiscal Year]
ELSEIF DateTimeFormat([Row+1:Fiscal Year],'%b %Y')=DateTimeFormat([Row+1:Current date],'%b %Y') THEN [Fiscal Year]
ELSE Null() ENDIF

This will compare the month and year of each field, rather than the exact date.

All comments

danrh

I'm not positive on the requirements, but is this more or less what you're looking for?

IF !IsNull([Row-1:Result]) THEN [Fiscal Year]
ELSEIF [Row+1:Fiscal Year]=[Row+1:Current date] THEN [Fiscal Year]
ELSE Null() ENDIF

Chandler_Bing

@danrh Thank you for the quick response.

That is exactly what i'm looking for however when I apply the formula in my workflow i'm not getting the output.

I'm attaching the workflow please tell me what do i need to change in it.

test_date.yxmd

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235