First in First out method to find the exact aging

Question

Hello everyone - I believe the community can support my alteryx requirement. M query is - whether i can do first in first out method to identify the exact aging for attached sample data.

Data contains 4 fields wherein 2 fields are important to do this calculation - Due date and Value

Positive value in value fields denotes debit value and negative fields denotes credit. I need to apply all credit values (negative) to offset the positive values in ascending order on due dates. I have provided the sample result for your better understanding.

let me know if you have any clarifications.

Alteryx sample.xlsx

vmanika8 · Answer

Total negative values irrespective of date should go on offset the positive values in date wise (ascending order). Result should bring the remaining data which should show the posiive value and the aging date

OTrieger · Answer

@vmanika8

I'm not totally sure what you want to achieve however from looking at the excel what I can see that you are using the total balance and then using the most current date to calculate aging. You can do it with Summarize tool, Sum the value and select Max Date, then use formula tool to get the number of days given the target date. You can use this formula to get the days DateTimeDiff(dt1,dt2,"days")