Calander for "calculation_periods"

[datanibbler]

Hi,

I am just creating INCLUDE files for a Standard mastercalendar in my new Company.

The complex Thing is that there are actually three kinds of periods:

- The normal calendar

- The "fiscal year" which starts On October 01

- The "booking" year where one period starts on the 16th of a month and runs till the 15th of the following month.

The first and second I have already implemented, that is not a Problem since the "boundaries" of a fiscal_period matches those of a calendar_month, thus the boundaries of one fiscal_quarter match those of a calendar_quarter (with an Offset of 3 months).

I was able to use the same functions - InMonthToDate and so on - for both.

The third is different and I have not yet found a way to use those same functions to compute it because the "boundaries" of the booking_period do not match those of a calendar_month, thus the quarters and years do not match, either.

As an example: Fiscal_year 2017 started on Oct 01, 2016 and will end on Sep 30, 2017 - but "booking_year" 2017 started on Sep 16, 2016 and will end on Sep 15, 2017 - on Sep 16, 2017 we will have "booking_period" 1 of the "booking_year" 2018 while still counting "fiscal_period" 12 of "fiscal year" 2017 for 15 more days.

I will try once again.

Can anyone else think of a way to to this?

Thanks a lot!

Best regards,

DataNibbler

[effinty2112]

Hi Friedrich,

Try

Mod(Month(Date-15)+3,12)+1

Cheers

Andrew

[datanibbler]

Hi Andrew!

That is really cool! It does not match the issue I was trying to solve, but it does solve another one - it makes the calculation of one field I already had in my calendar a lot easier, replacing a MAKEDATE() with two IFs inside.

Maybe I can use the same or a similar Approach to compute the start and end of the "booking_period" in which the selected value of >> Date << lies? Each of those would similarly replace a MAKEDATE() with two IFs inside.

Thanks a lot!

Best regards,

DataNibbler

[effinty2112]

Hi DataNibbler,

Don't these two expressions solve your problem?

BookingYear = if(Month(Date-15)<9,Year-1,Year)

BookingPeriod = Mod(Month(Date-15)+3,12)+1

Cheers

Andrew