day numbers

[Krish2459_58]

Hi, Below is the master calander we are using and the fiscal start month is = 7.

Here I want o calculate the daynumber starting month July every year.

Datefield:
LOAD date($(varMinDate)+IterNo()-1) AS DateNum_Fisacal
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
Set vFM = 7 ; // First month of fiscal year

Calendar:

Load * ,Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month_F2, fMonth) as FMonth; // Dual fiscal month

Load *,Year_F2 + If(Month_F2>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month_F2-$(vFM), 12)+1 as fMonth; // Numeric fiscal month

Load Year(DateNum_Fisacal) as Year_F2, // Your standard master calendar

Month(DateNum_Fisacal) as Month_F2,

DateNum_Fisacal

Resident Datefield;

[marcus_sommer · Accepted solution]

You may just use a main-logic like:

daynumberofyear(Date) - (makedate(Year, 7) - yearstart(Date))

respectively adjust the offset-value a bit maybe by subtracting a fixed value of about 182 and by considering any leapyear with a check of mod(Year, 4) = 0 or similar.

Another method might be to use interrecord-functions like peek() and previous() within a sorted resident load - maybe with an expression like:

if(Month = 7 and Day = 1, 1, peek('daynumber') + 1) as daynumber