2 Replies Latest reply on Aug 31, 2016 10:58 PM by Lance Martens

# Assistance required with configuring a Customised “Date” Calculated Field

Hi All,

I would appreciate any assistance with how to configure a Customised “Date” Calculated Field?

Logic:

• I have unique “Reporting Weeks”

Required:

• I am attempting to create a calculated field that will give me unique “Reporting Months” based on the grouping of the Reporting Weeks

• Calculation / Formula example of what I’m aiming for and I need help with please?

IF [Reporting Week] = 1, 2, 3, 4, 5 then "January"

ELSEIF [Reporting Week] = 6, 7, 8, 9 then "February".........etc. all the way to December

My workbook is attached.

Any assistance will be appreciated,

Cheers,

Lance

• ###### 1. Re: Assistance required with configuring a Customised “Date” Calculated Field

Hi Lance,

Please take a look at the solution. I don't think you need any calculation to fix this up. You can do it with the tableau date options which you should be using on the Calendar Date.

Please find the below attached screenshot to verify the same. Also find the attached tableau 10 workbook for the same for reference.

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: Assistance required with configuring a Customised “Date” Calculated Field

Hi Ashish,

Appreciate the feedback mate!

To explain my problem we will only evaluate the data for 2016 for now.

• I agree that if we compare the “Reporting Week” Dimension with the “Calendar Date Reporting Week” the production data is the same for each week.

• However, if we introduce a monthly component  based on the number of days in that month like in worksheet "Calendar Date Reporting Month" you can clearly observe how the “Calendar Date Reporting Week” for some months cross over between different months which it should not be doing.

• You can also observe the problem in Worksheet “Sheet 2 & 6” for February where Week 10 falls in February and March, there are also clashes in other months of the year

• The table below is a further illustration of how my 2016 weeks are grouped into the respective months:

 Month Weeks January 1,2,3,4,5 February 6,7,8,9 March 10,11,12,13 April 14,15,16,17,18 May 19,20,21,22 June 23,24,25,26 July 27,28,29,30,31 August 32,33,34,35 September 36,37,38,39 October 40,41,42,43,44 November 45,46,47,48 December 49,50,51,52,53

Required:

• I need a calculated field that will group the respective Weeks together to give me the twelve months

• Typically, a formula that will allow me to add, Week 1 to 5 to give me “January” and so on for all the other months of the year

• My workbook is attached, I have also attached the 2016 reporting weeks and months for context on what I am trying to replicate (see attached pdf)

Thanx again for your support Ashish!

Cheers,

Lance