6 Replies Latest reply on Oct 8, 2018 4:24 PM by Don Wise

# Creating decades from Years using calculated fields

I have a date dimension called [Year] and would like to group into decades [Decade] and keep as a date formatted dimension so that I can use in a line chart or any chat that will enable me to show trends. I was able to use the group function to create decades out of the dimension(by grouping years together) but I want  this to be to be done using date calculated field and also to create a forecast viz that will enable me to split the dimension (year) into quarters.

Attached is workbook that contains the Data set.

Your assistance will be highly appreciated.

Thanks

• ###### 1. Re: Creating decades from Years using calculated fields

Please see if below and attached (2018.2) workbook approach meets your needs? I did need to change your Dimension Year to Date in order to make this work.  If this answers your question, please mark this response as correct. Thx, Don  1 of 1 people found this helpful
• ###### 2. Re: Creating decades from Years using calculated fields

The only "Year" field I saw was "Year of Graduation", which was an integer, without quarter data. So I kept Decades as an int. 1 of 1 people found this helpful
• ###### 3. Re: Creating decades from Years using calculated fields

Thank you for your help, and that's what I am actually looking for. Please can you please provide a comment explaining what each character in calculated field means.

As for the second one, I want to create a forecast model, whereby I will be able to forecast gift amount for subsequent years, quarterly. let say i want to know expected total gift amount in 2nd quarter of 2016.

Thank you, I am really grateful.

• ###### 4. Re: Creating decades from Years using calculated fields

Thank you for your help. What does the % operator does in the calculated field?

thanks

• ###### 5. Re: Creating decades from Years using calculated fields

This is the modulus operator. It returns the remainder of the division.

1967 % 10 = 7, which is the remainder after dividing 1967 by 10

So 1967 - (1967 % 10) = 1967 - 7 = 1960.

The dataset you posted, is have already aggregated to year, so you don't have quarterly level data to use to forecast.

1 of 1 people found this helpful
• ###### 6. Re: Creating decades from Years using calculated fields

It is using the MakeDate function, taking the year from [Year of Graduation], then adding a month (1) and a day (1) to equal a date format: yyyy/d/m; then uses multiplication and division to group years into groups of 10.  The modulo function as Nick Parsons described will also function in the same manner.

As we both noted, there aren't any actual dates for [Year of Graduation], that field is already aggregated up and so we can't provide true 'quarters' as a result. When I cast it as a Date it made each year '1/1/yyyy' so a pseudo-quarter could be displayed. You'll need actual graduation dates in order for that to function in the manner you'd like.

Lastly Forecasting Help is located here: How Forecasting Works in Tableau  Not sure that it will work for what you're trying to do...

Hope that helps! Thx, Don