Number of days since expiration and to next expiration

[riancovorster]

Hi

I'm trying to:

1. calculate the days since the previous expiration
2. calculate the days till the next expiration

Step 1

The days to third Wednesday of the month for the current month is calculated. from checking the explore results this seems correct. The days to third Wednesday of the month for the current month can be negative if it has already passed for the month

Step 2

In order to calculate the days since the previous expiration and the days till the next expiration another calculation should be done.

If Step 1 is positive it will be assigned as days till the next expiration and number days in the previous month should be subtracted to get days since the previous expiration

If Step 1 is negative it will be assigned as days since the previous expiration and number days in the current month should be added to get days till the next expiration

When running the explore it seems to do it correctly for the 2nd IF statement however not the 1st If statement

Could someone please be so kind to assist me resolve this issue

// Days from and to expiration calculation

function DaysInMonth(MonthNum, YearNum)
{
       _Daysinmonth=IIf(MonthNum == 1 OR MonthNum == 3 OR MonthNum == 5 OR MonthNum == 7 OR MonthNum == 8 OR MonthNum == 10 OR MonthNum == 12, 31, 30);
       Daysinmonthfeb=IIf( (YearNum % 4 == 0 AND YearNum % 100 != 0) OR (YearNum % 4 == 0 AND YearNum % 400 == 0), 29, 28);
       _Daysinmonth=IIf(MonthNum==2, Daysinmonthfeb, _Daysinmonth);
       return _Daysinmonth;
}

	d = Day();
	wd = DayOfWeek();
	DaysToThirdWednesday = IIf(3-wd<0, (10-wd) % 7, (3 - wd) % 7);
	ThirdWednesday = ((d + DaysToThirdWednesday) % 7)+14;
    ThirdWednesday = IIf(ThirdWednesday==14, 19, ThirdWednesday); //corrects problem if the first of the month is a Saturday
    DaysToThirdWednesday = ThirdWednesday - d;
    
    
    if(DaysToThirdWednesday[0] > 0)
    {
		untilNext = DaysToThirdWednesday;
		MonthNumber = Month();
		MonthNumber = MonthNumber -1;
		sincePrev = DaysInMonth(MonthNumber,Year()) - DaysToThirdWednesday;
    }
    
    if(DaysToThirdWednesday[0] <= 0)
    {
    	sincePrev = abs(DaysToThirdWednesday);
		untilNext = DaysToThirdWednesday + DaysInMonth(Month(),Year());
    }
    
// Exploration
	
Filter = 1;

AddColumn(untilNext , "UntilNext", 1.4); 
AddColumn(sincePrev, "sincePrev", 1.4); 
AddColumn(Day() , "Day", 1.4); 
AddColumn(DayOfWeek() , "Day of Week", 1.4);
AddColumn(DaysToThirdWednesday , "DaysToThirdWednesday", 1.4); 

[riancovorster]

DaysToThirdWednesday[0] is the problem

it references the current date value in the array and not the value in that point in time