Year presence ratio between 2 dates

[psunny0070]

Hi all,

I'm seeking help to include in load script a calculated field we could name [Year Presence], starting from 2 date fields, [Start date] and [End date].

I'm actually trying to replicate something I have done in excel, but with quite complex formulas:

Start Date	End Date	2022 Presence
01/01/2022	30/06/2022	0,50
01/01/2022	31/03/2022	0,25

The context is members arriving on a given date and then leaving . I need to obtain the presence ratio on a given year.

(if no end date in the above examples, then value would be '1')

What would be the best way to obtain "Presence" value for a given year as a calculated field in the loading script?

Thanks a lot in advance for your help, as I'm running in circles.

[Marijn]

Hi,
You could consider using the following:

( (floor([End Date])+1) - floor[Start Date]) / ( floor(YearEnd([Start Date])+1) - floor(YearStart([Start Date])) )

That would be something like this with your data example:

((floor([30/06/2022])+1) - floor([01/01/2022]) / ( floor(YearEnd([01/01/2022])+1) - floor(YearStart([01/01/2022])) )
=
(44743 - 44562) / (44926 - 44562)
=
181 / 365 = 0,49589

This will give you the percentage of days present in that year.

Hope it helps!

[psunny0070]

Hi Marijn, 

Thank you so much for your reply.

It's a great start, but what should I add to the expression to only have results for 2022 presence? 

Because if I have a start date = 01/01/2021  and end date = 30/06/2022 for instance , with the present expression I obtain 1,49589 where I need 0,49589

Thanks again for your help

[Marijn · Accepted solution]

To make it flexible, so you don't have to change anything to the script next year, I'd use a variable to determine which year to calculate:

let vYear = year(today()) - 1

Then the expression should be something like this:

if(floor([Start Date]) < floor('01-01-$(vYear)') and floor([End Date]) > floor('31-12-$(vYear)'),   1,
    if(floor([Start Date]) < floor('01-01-$(vYear)'),   ((floor([End Date])+1) - floor('01-01-$(vYear)')    / ( floor('31-12-$(vYear)') +1) - floor('01-01-$(vYear)')),
    if(floor([End Date]) > floor('31-12-$(vYear)'),     ((floor('31-12-$(vYear)')+1) - floor([Start Date])  / ( floor('31-12-$(vYear)') +1) - floor('01-01-$(vYear)'))
    )))

Beware I didn't test the expression, so there might be a typo somewhere. The general approach is:

- if-statement 1: IF start date is before 1-1-vYear AND end date is after 31-12-vYear THEN 1,
- if-statement 2: IF start date is before 1-1-vYear, calculate number of days between 1-1-vYear and End date and divide by number of days in vYear
- if-statement 3: IF end date is after 31-12-vYear, calculate number of days between start date and 31-12-vYear and divide by number of days in vYear

[psunny0070]

Thanks a lot , I tried this but I now always obtain blank values, I wonder why?

[psunny0070]

Hi Marijn,

Just to let you know I finally obtained values with ('01/01/'&$(vYear)) and ('31/12/'&$(vYear)) instead.

But strangely I need to divide the whole calculated field by 365 to obtain the ratio, otherwise I just obtain the difference in number of days.

Thanks again for all your help here.