Finding Start and End date

[angel0v89]

Hi all,

I have got a use case that I really need help! 

I have got the following dataset which is on a monthly basis, for example 1 row per company per month.

Date	Id	Company Name	Value
01-03-2019	12345	Dan Ltd	2000
01-04-2019	12345	Dan Ltd	5000
01-05-2019	12345	Dan Ltd	10000
01-01-2019	67886	Alex Ltd	5000
01-02-2019	67886	Alex Ltd	0

...and so on.

So the idea is to create a Start date and End Date for each client based on the value. When the first value>0 exist to capture the Date as a start date and when it goes to zero to be end date.

From the above case: 

Dan Ltd - Start Date - 01-03-2019

Dan Ltd - End Date - (empty, since it is still current client) 

Alex Ltd - Start Date - 01-01-2019

Alex Ltd - End Date - 01-02-2019 

Many thanks in advance

[atcodedog05]

Hi @angel0v89 

Here is how you can do it.

Workflow:

Hope this helps : )

[angel0v89]

I have got a records where the End Date is before the start date? 

Looking at the data I got companies where 

2020-05-01   James Ltd 0
2020-06-01   James Ltd  0
2020-07-01    James Ltd 50000
2020-08-01 James Ltd  50000
2020-09-01 James Ltd  50000

[atcodedog05]

Hi @angel0v89 

What should be output for the above scenario?

[angel0v89]

Start date 2020-07-01 and End Date - None 

In case we have got 

2020-05-01   James Ltd 0
2020-06-01   James Ltd  0
2020-07-01    James Ltd 50000
2020-08-01 James Ltd  50000
2020-09-01 James Ltd  0

Then the End date will be 2020-09-01 

[angel0v89]

I think your solution works perfectly well on the Start Dates because it takes the MIN in case of Value!=0 but the End dates are problematic 

[atcodedog05]

Hi @angel0v89 

We can extend the workflow like below to meet the condition.

Workflow:

Hope this helps : )

[angel0v89]

I have tried but this won't work because if you have like: 

2020-05-01   James Ltd 0
2020-06-01   James Ltd  22222
2020-07-01    James Ltd 50000
2020-08-01 James Ltd  0
2020-09-01 James Ltd  0

It will have End date = 2020-09-01 where actually should be 2020-08-01

[atcodedog05 · Accepted solution]

Hi @angel0v89 

Try this updated workflow. Does it meet your condition?

Hope this helps : )

[angel0v89]

Works! Accepted as a solution. Thanks a lot