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.

[MOlinski]

I have uploaded the file incase you wanted to take a look, I just deleted the information in our confidential columns. 

[flying008]

Hi,@MOlinski 

In fact, there are 9 situations in your requirement that need to be judged and returned to the result, so the formula will be very complicated and is for reference only. I just made a sample for you.

iif(Contains([_CurrentFieldName_], "15"),
(IF DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y") <= [Benefit Begin] || DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y") > Dateadd([Approved Thru],15,"days") THEN 0
ELSEIF DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y") >= Dateadd([Benefit Begin],15,"days") && DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y") <= Dateadd([Approved Thru],15,"days") THEN min(15, DateTimeDiff([Approved Thru],Dateadd(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),-15,"days") ,"day")) * [Rate]
ELSEIF DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y") > [Benefit Begin] && DateTimeDiff(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),[Benefit Begin] ,"day") < 15 THEN DateTimeDiff(min([Approved Thru],DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y")),[Benefit Begin] ,"day") * [Rate]
ELSE -100 ENDIF),
(IF DateTimeDiff([Approved Thru], DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d"),"day") < 14 || DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y") <= [Benefit Begin] THEN 0
ELSEIF DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d") >= [Benefit Begin] && DateTimeDiff([Approved Thru], DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d") ,"day") > 14 THEN min(DateTimeDiff(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),Dateadd(DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d"),14,"days") ,"day"), DateTimeDiff([Approved Thru],Dateadd(DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d"),14,"days") ,"day")) * [Rate]
ELSEIF DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y") >= [Benefit Begin] && DateTimeDiff([Benefit Begin],DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d"),"day") > 14 THEN 
(DateTimeDiff(min([Approved Thru], DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y")) ,[Benefit Begin],"day")+1) * [Rate]
ELSEIF [Benefit Begin] < Dateadd(DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d"),14,"days") && [Approved Thru] > Dateadd(DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d"),14,"days") THEN DateTimeDiff(min([Approved Thru],DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y")),Dateadd(DateTimeFormat(DateTimeTrim(DateTimeParse([_CurrentFieldName_] + "-2022","%d-%b-%Y"),"Month"),"%Y-%m-%d"),14,"days") ,"day") * [Rate]
ELSE -100 ENDIF)
)

************************

1- Select Data

Benefit Begin

Approved Thru

Extended

Rate

15-Jan

31-Jan

Jan Total

15-Feb

28-Feb

Feb Total

15-Mar

31-Mar

Mar Total

2021-12-31

2022-1-23

0.33

2021-10-5

2022-3-28

0.44

2022-2-20

2022-4-9

0.44

2- Multi-Field Formula
Benefit Begin	Approved Thru	Extended	Rate	15-Jan	31-Jan	Jan Total	15-Feb	28-Feb	Feb Total	15-Mar	31-Mar	Mar Total
2021-12-31	2022-1-23		0.33	4.95	2.64		0	0		0	0
2021-10-5	2022-3-28		0.44	6.6	7.04		6.6	5.72		6.6	5.72
2022-2-20	2022-4-9		0.44	0	0		0	3.96		6.6	7.04

3- 2D Formula
Benefit Begin	Approved Thru	Extended	Rate	15-Jan	31-Jan	Jan Total	15-Feb	28-Feb	Feb Total	15-Mar	31-Mar	Mar Total
2021-12-31	2022-1-23		0.33	4.95	2.64	7.59	0	0	0	0	0	0
2021-10-5	2022-3-28		0.44	6.6	7.04	13.64	6.6	5.72	12.32	6.6	5.72	12.32
2022-2-20	2022-4-9		0.44	0	0	0	0	3.96	3.96	6.6	7.04	13.64

[OllieClarke]

Here's how I'd approach this:

I've assumed a couple of things, such as we should use the extended date where it exists, rather than Approved Thru. Also that you're only interested in leave taken this year.

And that the original data input doesn't include any of the monthly columns. 

Like @DataNath's approach, I filled in all days between the begin and approved/extended, then grouped them by 15th/last of month. I also found the monthly totals. We can then cross-tab this data counting the days in each group. 

Finally we make sure we haven't lost any records (e.g. people who have no leave this year), and then multiply everything by the Rate. Leaving us with this (after a bit of cleaning):

Hope that helps,

Ollie

[MOlinski]

This was extremely helpful. Thank you.