Calculate Quarter within Date Range

[Former Member]

I have question regarding a date range. I created a formula like so

{SO_SalesOrderHistoryHeader.OrderDate} >=DateValue (2008,04,01) and {SO_SalesOrderHistoryHeader.OrderDate}<=DateValue (2008,06,30)

I get my "True" which is fine, my problem is, what happens when it goes to the next year? Or I want to look at a previous year? Do I have to go back and edit my formula?

Or is there a way to tell my formula to go by whatever year is entered?

[Former Member · Accepted solution]

Well, you can have a parameter to input the year and then use it in your formula so that no matter what the value of the year, your formula will still work. Make the parameter for the year of type Number.

{SO_SalesOrderHistoryHeader.OrderDate} >=DateValue ({?Year Parameter},04,01) and {SO_SalesOrderHistoryHeader.OrderDate}<=DateValue ({?Year Parameter},06,30)

[Former Member · Accepted solution]

Well I already have a parameter to enter in the date range. I was using this to then create my quarter. I don't want to have to use two parameters. Is there a way to input my date range into my formula? I was trying to do that initially but I couldn't get any results out of it. Our accounting system doesn't use quarters but periods. So I was trying to create formulas that would determine a date range and then give me my quarters.

[Former Member · Accepted solution]

You can use the date range but the problem I forsee with it is which date are you going to use? The start date or end date? The choice also determines which date to use if the two dates span across years. For example, if your date range is 2008-10-01 to 2009-03-31.

If the above situation will not occur, then you can use the dates of your date range thus:

Modify your formula thus to use the year of the start date of your date range

{SO_SalesOrderHistoryHeader.OrderDate} >=DateValue (year(minimum(?date range})) ,04,01) and {SO_SalesOrderHistoryHeader.OrderDate}<=DateValue (year(minimum(?date range})) ,06,30)

Modify your formula thus to use the year of the end date of your date range

{SO_SalesOrderHistoryHeader.OrderDate} >=DateValue (year(maximum(?date range})) ,04,01) and {SO_SalesOrderHistoryHeader.OrderDate}<=DateValue (year(maximum(?date range})) ,06,30)

Hope this made things clear as mud

[Former Member · Accepted solution]

Awesome! That works! I appreciate your help on this.

Do you think there is a way to pull the year that is entered through this parameter?

I figured I would tell it what quarter it was in how do I add what year it is too?

if {SO_SalesOrderHistoryHeader.OrderDate} >=DateValue (year(minimum({?Date})) ,01,01) and {SO_SalesOrderHistoryHeader.OrderDate}<=DateValue (year(minimum({?Date})) ,03,31) then

"Quarter 1"