Days difference funds lying Idle

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.

1240503_gawa_REV2.yxzp

hi @Vapour02

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

1240503_gawa_REV4.yxzp

All comments

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.

1240503_gawa.yxzp

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

@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

@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.

@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.

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.

1240503_gawa_REV1.yxzp

@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.

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.

1240503_gawa_REV2.yxzp

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

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.

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.

1240503_gawa_REV3.yxzp

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

hi @Vapour02

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

1240503_gawa_REV4.yxzp

@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.

Revised_Alteryx_Sample_data_.xlsx

@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!

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.

@Vapour02 I have to say that I would stop my support at this point. I've been developing total 5 WFs according to your request, and I guess you are almost ready to finish your work by yourself! Good luck!