Is there a good way to add/subtract quarters from the current date?

Dan_Rosen · 2025-03-14T15:36:09+00:00

There was an error rendering this rich post.

Dan_Rosen

For example, sales 1 quarter ago, sales this quarter, sales next quarter, etc..

I noticed that you can't use Quarter Seq Number in the date functions.

Find more posts tagged with

Accepted answers

All comments

Dan_Rosen

I ended up calculating it manually for now, taking the measure where it's:

greater than or equal to:

DateAdd(Month,0, DateTime(Format(DateAdd(Month, IIF(DatePart(Month, NowDate) IN (1,4,7,10),0, IIF(DatePart(Month, NowDate) IN (2,5,8,11), -1, -2)), NowDate),'MM/01/yyyy')))

and less than:

DateAdd(Month,3, DateTime(Format(DateAdd(Month, IIF(DatePart(Month, NowDate) IN (1,4,7,10),0, IIF(DatePart(Month, NowDate) IN (2,5,8,11), -1, -2)), NowDate),'MM/01/yyyy')))

Then modifying the 0 and 3 values as needed to -3/0, 0/3, 3/6, etc.

If anyone thinks of a more optimal way, please let me know 😁

Diane McElhiney

Dan,

Hopefully, you created your expression as a Custom Attribute associated with Time. Below, I'm sharing Custom Attributes for various levels in the Time hierarchy in case anyone finds them useful.

Quarter Start Date Flag (time.day)

IF (DatePart(Month,[Date]) = 1 OR DatePart(Month,[Date]) = 4 OR DatePart(Month,[Date]) = 7 OR DatePart(Month,[Date]) = 10) AND DatePart(Day, DateAdd(Day,1,[Date]))=2
Then 'Y'
Else 'N'
END IF

Quarter End Date Flag (time.day)

(DatePart(Month,[Date]) = 3 OR DatePart(Month,[Date]) = 6 OR DatePart(Month,[Date]) = 9 OR DatePart(Month,[Date]) = 12) AND DatePart(Day, DateAdd(Day,1,[Date]))=1

Then 'Y'

Else 'N'

END IF

Quarter Start of Month (time.month)

([Quarter Number] % 10 - 1) *3 + 1

Quarter End of Month (time.month)

([Quarter Number] % 10) *3

Enrico Menotti

Hi Dan,

I would play around with Positional Calculations:

https://docs.infor.com/birst/2024.x/en-us/birstdataandmodels/ClassicBirst/Modeling/BQLandExpressions/Positional_Calculations.html

and Dimensional Expressions:

https://docs.infor.com/birst/2024.x/en-us/birstdataandmodels/ClassicBirst/Modeling/BQLandExpressions/Dimensional_Expressions.html

Maybe in combination with Custom Attributes. Also, from what I have seen, in Dimensional Expressions you may also use Saved Expressions.

Enrico