Calculation in Set Expression does not work correctly when selecting non-consecutive dates

[JayG]

Using payroll timekeeping data, I built a set expression to give me the number of FTE (Full-Time-Equivalent) employees, based on dates selected, where, for example 40 hours over a 7 day period equals 1 FTE, 80 hours over a 7 day period equals 2 FTE's, 300 hours over a 28 day (Feb 2021) period equals 1.875 FTE's.

[ 1.875 = (300/40) / ((Max date of 2/28/21 - Min date of 2/1/21)+1)/7 ) ]

The expression I am using is:

=((Sum({$<[Hour Code]>} Hours))/40) 
/ 
((Max(Date)- Min(Date)+1)/7) 

The fields in my timekeeping data used in the expression are 'Hour Code', 'Hours' and 'Date'.

This works fine to return a correct FTE calculation when the dates selected are consecutive, but I have a Filter Chart that allows selection by whole years and if I select two non-consecutive calendar years  (For example 2018 & 2020, skipping 2019) the calculation produces incorrect results.  This calculates a smaller than expected # because it is dividing my calculation by the full time period between the two selected years, which includes 2019, but I only want the calculation based upon calendar years 2018 & 2020, not 2019. 

How do I write a set statement using my example above, to exclude 2019 in the FTE Calculation?

[chrismarlow]

Hi,

I've not tested this (& it would only work for 2 selected years), but maybe change your denominator to;

((((Max(Date)- MakeDate(Year(Max(Date)), 1, 1)+1)+(MakeDate(Year(Min(Date)), 12, 31)- Min(Date)+1)))/7)

Cheers,

Chris.

[stevejoyce]

How about dividing by Count(Distinct [Date]), so you are actually counting # of Days.

[JayG]

Thanks. However, this still does not solve the issue because it will not account for dates that are missing from the actual data (like no hours are recorded being worked on the weekend, thus there will be no rows of data for some Saturdays and Sundays when no one worked and thus the Distinct #'s of calendar dates would not include those weekends when no one worked). I need the denominator to record the total possible days for the whole year(s) selected without regard to whether selection of 1 or more years is non-continuous or not. 

[JayG]

Thanks. Unfortunately I need this to be able to calculate  any number of selected years represented in the data and not just 2 years. 

[chrismarlow]

Hi,

If you knew you would always be selecting full years you could try something like;

=$(='Date(''31/12/'&GetFieldSelections(Year,''')+Date(''31/12/')&''')')
-$(='Date(''01/01/'&GetFieldSelections(Year,''')-Date(''01/01/')&''')')
+Count(DISTINCT Year)

As the denominator. Immediate issue, what if you only have partial years? The below nearly works, but I think falls over where you have weekends at the start/end of year;

=$(='Date(''31/12/'&GetFieldSelections(Year,''')+Date(''31/12/')&''')')
-$(='Date(''01/01/'&GetFieldSelections(Year,''')-Date(''01/01/')&''')')
+Count(DISTINCT Year)
-(Date(Min(Date))-Date('01/01/'&Year(Min(Date))))
-(Date('31/12/'&Year(Max(Date)))-Date(Max(Date)))

So would then need a layer in it to cope with that.

At this point, I would stop trying like this & think about how to write a script that would give me a UDF I could call for this, or see if someone has a smarter way, as it is getting painful 😀.

Attaching the QVF ... in case it is of any use.

Cheers,

Chris.