Days difference funds lying Idle

[Vapour02]

Hello all professionals, I need help to calculate count of days the funds are lying idle. If the amount is not added for several days. Also if the amount is added it should start with zero again until the next amount is added for the date. Attached is the sample data for understanding. 

[gawa]

hi @Vapour02 

My solution is using Sort and Tile technique like this.

First, sort data by Date in ascending order, and put a tile number by Tile tool.

Second, if amount is NOT 0, then replace tile seq number to 0.

Third, revert to the original order by another Sort tool.

[Vapour02]

@gawa Thanks, but can you tell what did you select in the Tile tool to get the desired output.

[gawa]

@Vapour02 Could you open the workflow file attached in the above post? Then, you can confirm configuration of Tile tool.

For your ready reference, config is as follows:

Tile method: Unique value

Unique field: Amount

*Check leave unsorted

[Vapour02]

@gawa Thank you. I was unable to open it. However the output is not coming as ai require. For example 2023-10-17 minus 2023-10-08 should come as 10 and so on  

[Rhys_Cooper]

**retracted**

[Rhys_Cooper]

@Vapour02  sorry forgot to tag you in my solution.

[Vapour02]

@Rhys_Cooper Thank you so much for your time. However the solution @gawa provided is near to correct. I have used the multi row formula tool before. However I also need to consider the Amount, I need to calculate the difference between two debit amounts. So if one amount is added on 2023-10-07 and another is added on 2023-10-17, I need the difference between these two and then again the difference between the amount added on 2023-10-17 and 2023-10-19 and then again the actual difference between 2023-10-19 and the amount added on 2023-10-27 and so on. So I get the more accurate days difference between two amounts.

[Jonathan-Sherman]

Hi @Vapour02,

Should lines for missing dates also be added? For example there's a missing date for 2023-10-10? And the difference between the debit amounts, could you give an example of the output you'd be looking to get to from the data you shared?

Kind regards,

Jonathan

[gawa]

@Vapour02 

This is the revised WF so as to meet you requirement.

- Multirow Formula is used to trim first record to 10 (I guess it should be 0 because amount is not 0, but anyway as you told...)

- Padding the missing dates like 2023-10-10 by Generate Row tool (thank you @Jonathan-Sherman , I didn't notice there exists missing date)

Can't you open my WF? It is sometimes because of difference of Designer version between yours and mine. Try to proceed to open the file even when some pop up window appears. Usually, you can open it by doing so.