Stop a countdown of days based on a column

[dcsrobayo]

Hello,

I am trying to calculate the ageing of some services which are identified by ID. To calculate the ageing of each service I did the formula: DateTimeDiff([Initial_Date],Datetimetoday(),"days"). However, once the column "Status" is Complete, the ageing calculation needs to stops and I have to keep the count days between the Initial Date (which is fixed) until the last day which was pending the service.

Does anyone knows how can achieve this result?

Thanks

[TomWelgemoed]

Hi @dcsrobayo ,

I think you're halfway there.

The only bit you need is to know what the latest Pending date was and then to put it into a conditional statement. Please see the attached example and the image below.

Regards,

Tom

[dcsrobayo]

Hi @TomWelgemoed           

Thanks for your response. I applied into my model but when all the status rows are under "Complete" the join does not generate any result because there is not pending status. In addition, the column Latest_Pending_Date column disappears. Do you know there is another way to do it?

Many thanks,

[TomWelgemoed]

Hi @dcsrobayo ,

can you give me a dummy data example where you are stuck?

Many thanks,

Tom

[dcsrobayo]

Hi @TomWelgemoed

Please find attached the model of how it looks the results yesterday and how it will look today. Because, for example, a service can change within a day from pending to complete, when I used the model proposed, they will not be any pending service, therefore they will not be any result.

In addition, the field "Date_Time_Today" is dynamic because to set this field I used the formula Datetimetoday().

I hope this helps.

Many thanks,

Diana

[TomWelgemoed · Accepted solution]

Hi @dcsrobayo (Diana),

Thanks for the example.

OK, then the issue is that you're effectively "losing" data in the latest file, unless you are still able to retrieve the previous day's file?

I'm going to assume that you can still access the previous day's file - otherwise you would have to look to store copies yourself to maintain history. Which is important to be able to tell the latest date of any record.

I've made the example a little more complex so that we can simulate some scenarios where records are changing from 1 day to the next. Please see the attached workflow - there are 2 containers:

1. What you'd need to do on the first day to create the baseline

2. What you would do the following day to use that baseline

Let me know if you have questions - I didn't add duplicate records of the same ID, but this should work for that too, barring some potential duplication which you'd need to manage.

[dcsrobayo]

Thanks Tom. I had to made minor alterations but the model worked.

Many thanks for your help.

Diana

[TomWelgemoed]

Hi @dcsrobayo (Diana),

That's great to hear - it's a tricky thing to have to manage so happy if you're on the right track now.

Stay safe,

Tom