Day Counter

MOlinski

I am currently trying to create a report that will calculate the vacation accrual for employees on short-term disability and need help with the following:

Highlighted below is an example of what we currently show/calculate on our report.

So the columns "Benefit Begin" & "Approved Thru" are the dates that the employee is on leave. And it is also broken down by pay period, so for example from 1/1/2022-1/15/2022 is one period, and 1/16/2022-1/31/2022 is another period. 

So following the highlighted row, the employee's leave is from 2/20/2022-4/9/2022. With this, the Feb 28th period has the figure 3.96 entered. The way we get to this figure is we take the amount of days within that leave, so from 2/20-2/28, which gives us 9, and we multiply that figure by the "Rate" which is in the 4th column.

I am trying to add a formula like this in Alteryx and am having trouble. If anyone could assist that would be greatly appreciated.

Thank you!

DataNath

Don't have Alteryx open at the moment so just spit-balling, however...

Not sure how your full sheet is set up i.e. whether or not your pay periods are in the rows rather than headers. If so, you could perhaps generate rows, adding one day for each until you reach the 'Approved Thru', in order to create a range of the annual leave starting vs ending. After that, for each row check whether or not that day <= the end of each period (something like 'if [Benefit Begin w/generated rows] =< pay period end date then 1 else 0') and then summarize, grouping by Benefit Begin & pay period to get your count of days in each and then multiply that by the rate.