Hi Jesse,

drag the quarter into row and then drag week num into row.

Now we can get the quarter based weeks only.

Thanks

sankar

Hi  Jesse yung ,

1) Create a calculation like below

Name: Week Number

Syntax: datepart('week', [Date_field])

In the above  calculation "Datepart" is one of the Date Calculation Functions.

so we get the output like  1,2,3,---------------------52 ,53

2) Then add another calculation

Name: Week of the Quarter

Syntax: [Week Number]%13

In the above  calculation "%" is the Modulo Function

3) Then drg the Year, Quarter, Week Number, Week of the Quarter

4) Now we get your expected result.

================================

We have an alternative approach is using Index() function & adding the Addressing , Partitions accordingly.

So Numbers re starts from every Quarter

I hope it will help you.

Best Regards

Laxman Kumar

1) Create a calculation to get the Week Numbers,

2) Create another calculation to get the serial No,

3) Add the Index() to the work area, then convert to Discrete.

4) Right click on "Index()", then define the Addressing, Partitions accordingly.

Now we get the Required Week Numbers, Week numbers are restarting at Quarter Level.

I hope it will help you.

Best Regards

Laxman Kumar

Hi Laxman

Laxman Kumar B   Can you help me with my calculation?   I followed the instruction you gave but the results aren't exactly what I was looking for but ITS REALLY CLOSE!!!

As you can see in the screenshot below, the calculation you suggested (bottom row) is fine until it gets to the end of the quarter. I want the week in 1-14 regardless of which quarter we are in.   Each quarter has 14 weeks, but the calculation you suggested stops at week 12 and starts again at 0 when it should be week 13. 

I don't know how to adjust the formula to get that change.  I can't use the index() option since my data has gaps.  If I use index(), it may show as week 1 when it should be week 2 because week 1 has no data. 

Much appreciated!!!!

Best,

Jason

Hi Jesse yung,

I found a solution that works for me. I got the idea from here: excel function for week as per defined quarter - Microsoft Community

Logic to find the Week of Quarter:

1 + (WeekNUM of date - WeekNUM of 1st day of Quarter)

Find the following:

1. Weeknum of current date: DATEPART('week',[Creation Date])

2.Weeknum of 1st day of Quarter: DATEPART('week',DATETRUNC('quarter',[Creation Date]))

Week of Quarter:

1+(DATEPART('week',[Creation Date])-DATEPART('week',DATETRUNC('quarter',[Creation Date])))

Thanks PinakiBrahma for the idea!