Quarter to date

[#1]

I'm trying to get the right month for quarter to date calculations but my expression does not work.

In the script I want to set the following variable:

Set Y2DQ = (vSliderYear - Year(vCurrentDate)) * 12 + (if(vSliderQuarter, 1,1,(if(vSliderQuarter,2,4,(if(vSliderQuarter,3,7, 10)))))) - Month(vCurrentDate);

Assume I want to calculate the first month of the 2nd quarter of 2006 with the current date set to 31-03-2008 .

2006 - 2008 = -2 * 12 = -24

+

4 (vSliderQuarter is set to 2 so the if statement will translate it to 4 (I think this does not work properly)

-

3 (month(vCurrentDate = 3)

=

- 23 .. exactly the answer I need but I think something in the if statement is wrong

[#2 · Accepted solution]

Hi William,

so far I see it´s worth to have a look at:

InQuarterToDate ( date, basedate , shift [, first_month_of_year = 1] )

returns true if date lies inside the part of the quarter containing basedate up until and including the last millisecond of basedate. The quarter can be offset by shift. Shift is an integer, where the value 0 indicates the quarter which contains basedate. Negative values in shift indicate preceding quarters and positive values indicate succeeding quarters. If you want to work with (fiscal) years not starting in January, you may indicate a value between 2 and 12 in first_month_of_year.

Examples:

inquartertodate ( '2006-01-25', '2006-01-25', 0 ) returns true

inquartertodate ( '2006-01-25', '2006-01-24', 0 ) returns false

inquartertodate ( '2005-12-25', '2006-02-01', -1 ) returns false

Just an idea.

Rainer