4 Replies Latest reply on Aug 22, 2016 7:41 AM by bill.merlavage

# Display data in Quarters, but not true Quarters

I would like to display data in 3-month 'quarters', but not true quarters.

For example, assuming TODAY() = 8/22/2016, I would like to group my data as following:

5/1/2016 to 7/31/2016 = Q4

2/1//2016 to 4/30/2016 = Q3

11/1/2015 to 1/31/2016 = Q2

8/1/2015 to 10/31/2016 = Q1

Further, I would like to extend this logic to look at previous years.

5/1/2015 to 7/31/2015 = Q4-prev

2/1//2015 to 4/30/2015 = Q3-prev

11/1/2015 to 1/31/2015 = Q2-prev

8/1/2014 to 10/31/2014 = Q1-prev

etc...

I've tried using Bins, which almost worked but when spanning multiple years, Tableau creates separate a '0' grouping and a '12' grouping (which should be combined)  I'm able to capture the yearly data using the following, which allows me to break ii into every increment except these 'quarters':

DATETRUNC('month', DATEADD('month',-12, TODAY())) <= [Enc Date] AND [Enc Date] <= TODAY()

DATETRUNC('month', DATEADD('month',-24, TODAY())) <= [Enc Date] AND [Enc Date] < DATETRUNC('month', DATEADD('month',-12, TODAY()))

DATETRUNC('month', DATEADD('month',-36, TODAY())) <= [Enc Date] AND [Enc Date] < DATETRUNC('month', DATEADD('month',-24, TODAY()))

A possible solution would be using Tableau's built-in Fiscal Year Start feature, but I need to have this set dynamically based on TODAY() rather than hard-coded.

Does anyone have any suggestions?

Thanks,

Bill

• ###### 1. Re: Display data in Quarters, but not true Quarters

I'd build a dimension based in date field.

For example, assuming TODAY() = 8/22/2016, I would like to group my data as following:

5/1/2016 to 7/31/2016 = Q4

2/1//2016 to 4/30/2016 = Q3

11/1/2015 to 1/31/2016 = Q2

8/1/2015 to 10/31/2016 = Q1

Further, I would like to extend this logic to look at previous years.

5/1/2015 to 7/31/2015 = Q4-prev

2/1//2015 to 4/30/2015 = Q3-prev

11/1/2015 to 1/31/2015 = Q2-prev

8/1/2014 to 10/31/2014 = Q1-prev

If [Enc Date]>=DATEADD('month',-4, TODAY()) and [Enc Date]<=DATEADD('month',-1, TODAY()) Then 'Q4'

Else

....

I'm not considering days. You need to adjust with datetrunc, but you build all of your quarters in the some calculation and use it to break your values.

1 of 1 people found this helpful
• ###### 2. Re: Display data in Quarters, but not true Quarters

Thank you very much...this formula seems to be working:

If     [Enc Date]>=DATETRUNC('month', DATEADD('month',-3, TODAY())) AND [Enc Date] <= TODAY() Then 'Q4'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-6, TODAY())) Then 'Q3'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-9, TODAY())) Then 'Q2'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-12, TODAY())) Then 'Q1'

ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-15, TODAY())) Then 'Q4'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-18, TODAY())) Then 'Q3'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-21, TODAY())) Then 'Q2'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-24, TODAY())) Then 'Q1'

ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-27, TODAY())) Then 'Q4'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-30, TODAY())) Then 'Q3'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-33, TODAY())) Then 'Q2'
ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-36, TODAY())) Then 'Q1'

END

1 of 1 people found this helpful
• ###### 3. Re: Display data in Quarters, but not true Quarters

You need to suffix as you did before: -prev.

If not you'll mix periods.

Is it ok for you?

1 of 1 people found this helpful
• ###### 4. Re: Display data in Quarters, but not true Quarters

Noted.  Actually my plan is to give the quarters more dynamic names...such as 'Q4 ending: ' + str([some sort last day of the quarter date calc]), but I haven't figured that piece out yet.

Thanks again for your help.