Define start of fiscal year

Thanks Paul. I’ll try it out!

I guess I need help with the syntax of looking up the year(tempDate) using the select command.

I have created a Table called ‘FiscalYear Offset Days’

The table consists of two fields: ‘Calendar Year’ (a date field that I just put January 1, YYYY) and ‘Offset Days’ (number field with the number of offset days).

I’ve been trying to figure out a look up formula for a few hours and can’t seem to figure it out. Obviously, the magic happens within…

“…look up year(tempDate) in the new table by using the select command to get the offset days for that year.”

I’ve tried…

let offset := select FiscalYear.’Calendar Year” = year(tempYear)

let offset := select FiscalYear.’Offset Days’ where 'Calendar Year'= year(tempYear)

and many other combinations, but mostly just getting error messages and nothing returning the offset value.

How do I use the select command to look up the information in the look up table and return the offset value?

Okay, I am finally able to look up and display the offset days with the following script, where ‘Pay Period End’ is a date field for the start of the pay period (which should always be a Monday); FiscalYear is the look up table; ‘Calendar Year Number’ is a number field with the appropriate year typed in; ‘Offset Days’ is a number field for the number of days before the first Monday of that year.

let tempDate := year('Pay Period Start');
let offset := (select FiscalYear)['Calendar Year Number' = tempDate].'Offset Days';

but…

let fiscalYearWeek:=yearweek(tempDate-offset)

Results in the error

invalid operator: date - [number] at line 3 column 56

I’m continuing to see if I can figure it out!

Actually,

let fiscalYearWeek:=yearweek(tempDate-offset)

should actually be…

let fiscalYearWeek := yearweek(‘Pay Period Start’ - offset)

but I still get the same invalid operator error.