10 Replies Latest reply on May 10, 2012 11:48 AM by Sorbsbuster

# Calculating Week of Quarter

### Title

Calculating Week of Quarter

### Post

Hello,

Is there a way to calculate what week of the quarter the current date is in?

I know how to calculate which quarter the current date is in, but i need it more narrowed down somehow.

• ###### 1. Re: Calculating Week of Quarter

If you knew the start date of the quarter, could you not work out its week number, then subtract it from the current date's week number?  or, if you know the quarter the current date is in, then subtract 13, 26, or 39 from it, as appropriate?

• ###### 2. Re: Calculating Week of Quarter

I do not know the start date of hte quarter.

The only thing that I know is how to get the quarter itself.

Celing ( Month ( Get ( CurrentDate ) ) / 3 )

Which returnes 1, 2, 3, or 4 for the quarter.

So as of today, we are in quarter 2.

I'm just trying to figure out how to calculated what week of quarter 2 we are in.

• ###### 3. Re: Calculating Week of Quarter

Date for Q1: Date ( 1 ; 1 ; Year ( get ( currendate ) ) )

For Q2, its: Date ( 4 ; 1 ; Year ( get ( currendate ) ) )

and so forth.

You can use this inside a choose function:

WeekOfYear ( Yourdate ) - WeekOfYear ( choose ( Quarter ; "" ; Date ( 1 ; 1 ; Year ( get ( currendate ) ) ) ; Date ( 4 ; 1 ; Year ( get ( currendate ) ) ) ; //and so forth ) ) + 1

Should tell you what week of the quarter you are currently in but if the start of the quarter falls on a Saturday, week 1 of your quarter will be a single day....

• ###### 4. Re: Calculating Week of Quarter

Thanks Phil,

What is the "quarter" in your calcuation ((choose ( Quarter ; "" )?

• ###### 5. Re: Calculating Week of Quarter

Ceiling ( Month ( Get ( CurrentDate ) ) / 3 )

I've assumed that you already have a field where this value is calculated.

• ###### 6. Re: Calculating Week of Quarter

Thank you again.

Can you please check this and tell me what i'm doing wrong. My calculation is currently reading out 20. Which i'm assuming is 19th week of the year plus 1.

WeekOfYear ( d_Current Date ) - WeekOfYear ( Choose ( d_Current Quarter ; "" ; Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ; Date ( 4 ; 1 ; Year ( Get (CurrentDate ) ) ) ;  Date ( 7 ; 1 ; Year ( Get (CurrentDate ) ) ) ; Date ( 10 ; 1 ; Year ( Get ( CurrentDate ) ) ) ) )  + 1

My calculations d_Current Date is correct and produces the current date. And d_Current Quarter is correct as well which produces 1, 2, 3 or 4 based on which quarter the current date is in.

• ###### 7. Re: Calculating Week of Quarter

I copied your calculation pasted it into Advanced's data viewer and then put it inside a Let function to assign values to d_Current Date and d_current Quarter:

Let ( [ d_Current Date = Get ( CurrentDate ) ;
d_Current Quarter = Ceiling ( Month ( d_Current Date ) / 3 )
];
WeekOfYear ( d_Current Date ) -
WeekOfYear ( Choose ( d_Current Quarter ; "" ;
Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ) ;
Date ( 4 ; 1 ; Year ( Get (CurrentDate ) ) ) ;
Date ( 7 ; 1 ; Year ( Get (CurrentDate ) ) ) ;
Date ( 10 ; 1 ; Year ( Get ( CurrentDate ) ) ) ) )
+ 1 )

And I get 6 as a result.  (19 - 14 + 1 )

check d_Current Date to make sure that the value is of type date. You might also modify your expression to use Get ( CurrentDate ) in place of d_current date as a test.

• ###### 8. Re: Calculating Week of Quarter

I thought

WeekOfYear ( DateField ) -

Case (

WeekOfYear ( DateField ) > 39 ; 39 ;

WeekOfYear ( DateField ) > 26 ; 26 ;

WeekOfYear ( DateField ) > 13 ; 13 ;

0

)