-
1. Re: how to calculate week of the quarter
Sankarmagesh Rajan Apr 7, 2015 12:50 AM (in response to Jesse yung)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
-
2. Re: how to calculate week of the quarter
Tableau kumar Apr 7, 2015 2:14 AM (in response to Jesse yung)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
-
3. Re: how to calculate week of the quarter
Tableau kumar Apr 11, 2015 11:08 PM (in response to Jesse yung)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
-
4. Re: how to calculate week of the quarter
Jason Cheung Oct 19, 2016 4:31 AM (in response to Tableau 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
-
5. Re: how to calculate week of the quarter
Jason Cheung Oct 19, 2016 4:30 AM (in response to Jesse yung)1 of 1 people found this helpfulHi 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!