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. 

[Vapour02]

@gawa thank you so much for the solution and your time much appreciated. Also that date is actually missing because there is no transaction on that day. So it should be calculated as it is without adding any missing dates. This is just a sample I have a large data to work on and there can be multiple missing dates. So I needed it to calculate like that. However rest has worked perfectly fine with the idle days. I just don't need the missing date to be added. 

[gawa · Accepted solution]

@Vapour02 

OK, you need to calculate Idle Days considering missing dates, but actually not necessary to have them as records.

Then, you just have to JOIN with the original date, and SORT by date. That should be OK.

[Vapour02]

@gawa Thank you so very much. This totally worked. I appreciate all your help and patience. 

[Vapour02]

Hello @gawa , I needed one more help with this logic. Now I have to exclude the amount less than 1000 in Amount and calculate the rest. So 118 in Amount should be excluded and rest should be calculated. For example. 2023-10-27 minus 2023-10-18 and then 2023-10-27 minus 2023-10-19 and then so on. Can you please help me with that. Thank you in advance.

[gawa]

@Vapour02 

That is new requirement for me. I'm not sure we have to exclude all of rows having amount less than 1000(it means amount=0 also) or exclude sets starting from amount less than 1000 (not 0) and ending at amount >=1000...

Anyway, I prepared 2 patterns so please check it out, and pick either one that better fits to your demand.

[Vapour02]

Hello @gawa I guess ,I was unable to explain properly, sorry. I meant exclude as in the line items should be there in the data just when the difference of days is calculated it should no calculate the date which has amount less than 1000 so the difference should be calculated between 100000 and 400000.

For example. 2023-10-27 minus 2023-10-18 = 9 then 2023-10-27 minus 2023-10-19 = 8  then 2023-10-27 minus 2023-10-20 = 7 then 2023-10-27 minus 2023-10-21 = 6 and so on ahead. Thanks in advance 

[gawa · Accepted solution]

hi @Vapour02 

If I could understand your requirement correctly, this should be solution.

[Vapour02]

@gawa Yes sir, this worked. Thank you, much appreciated. However I'm facing a new challenge now since this was only for one account I might have multiple accounts in future. I tried to run the same solution on multiple accounts but it's messing up the data and the output. Attached is the sample for your reference. Also there are some missing dates so we don't need to add them just need to calculate as it is. I tried it with the tile and generate rows tool but I think I'm missing something and it's not working. Can you please help me with that it would be much appreciated. 

[gawa]

@Vapour02 It is not difficult. You just need configuration for "Group By" by "account" in tool configuration of every tools  that option is available.

Also, take care of Sort order, too(first, sort by amount, second by date,,,)

With the above hints, let's try to finish your work!

[Vapour02]

Hello @gawa this is working, but somehow it is adding the missing dates. So the data is increasing in numbers, for example 2023-10-10 is missing in account 1234 and 2022-09-08 is missing in account 5678 so it should be calculated as it is without adding the missing dates.