4 Replies Latest reply on Jan 13, 2019 5:03 PM by Mohammed Pareed

# How  to create a week bracket and Calculate the Profit within those week bracket

Hello All

I am trying to create a week bracket out of the data columns. Can somebody help me with this as its quite critical . The way i want is from the Date dimension i want to create the week brackets like 0-7 should display 1 week then 8-56 weeks should display 2-8 weeks 57 to 105 should display 9-15 weeks 106 to 154 should display 16 to 22 weeks 155 to 203 should display 23-29 weeks 204 to 252 should display 30-36 weeks 253 onwards should display 37+ weeks like that

i am having hard time in finding a method to do this. If anyone can suggest based on the superstore data and count the  profit with those above age bracket that would be very very helpful

Deepak Rai

Andy Kriebel

Carly Capitula

Jonathan Drummey

Regards

Larsen

• ###### 1. Re: How  to create a week bracket and Calculate the Profit within those week bracket

Hi Larsen,

You can try following:

Step 1: Create a calculated field 'DayOfYear' with following calculation:

DATEDIFF('day',DATETRUNC('year',[Order Date]),[Order Date])+1

This give you the day of Year.

Above calculation tells the day of year for the given date.

For instance, if order date is 6 Jan 2018, DayOfYear will be 6.

If order date is 5 Feb 2018, DayOfYear will be 36.

Step 2: Use 'DayOfYear' field to create 'Week Bracket' as below:

CASE TRUE

WHEN [DayOfYear]<=7 THEN '1 WEEK'

WHEN [DayOfYear]>=8 AND [DayOfYear]<=56 THEN '2-8 WEEKS'

WHEN [DayOfYear]>=57 AND [DayOfYear]<=105 THEN '9-15 WEEKS'

WHEN [DayOfYear]>=106 AND [DayOfYear]<=154 THEN '16-22 WEEKS'

WHEN [DayOfYear]>=155 AND [DayOfYear]<=203 THEN '23-29 WEEKS'

WHEN [DayOfYear]>=204 AND [DayOfYear]<=252 THEN '30-36 WEEKS'

WHEN [DayOfYear]>=253 THEN '37+ WEEKS'

END

Based on which range the DayofYear belongs to, you will get your week bracket for the given date.

Hope this helps.

Let me know your tableau version, I will provide a sample workbook if required.

Regards,

Mohammed

• ###### 2. Re: How  to create a week bracket and Calculate the Profit within those week bracket

HI Mohammed

My tableau version in 9.0 .

• ###### 3. Re: How  to create a week bracket and Calculate the Profit within those week bracket

Hi Mohammed

the formula mentioned by you above worked for me. Thanks for all the help

• ###### 4. Re: How  to create a week bracket and Calculate the Profit within those week bracket

Glad to know I could help.