5 Replies Latest reply on Jan 18, 2019 8:15 AM by nick.jordan.0

# Sum measure from latest date, and from 3 years prior calculations make no sense

I am looking to calculate the growth rate between the latest date and 3 years prior.

I have been successful at:

- Selecting the latest date as a calculated field called Latest Date

{MAX([Date])}

- Summing the count based on current date called Count Latest

IF [Date] = [Latest Date]

THEN [Count]

ELSE 0

END

- Summing the count based on current date 3 years prior called Count 3 Years Prior

IF [Date] = DATEADD('year', -3, [Latest Date])

THEN [Count]

ELSE 0

END

- Creating a difference between these values

[Count Latest] - [Count 3 Years Prior]

When I create a new measure which involves any type of division ex.

(([Count Latest]/[Count 3 Years Prior])^(1/3)) - 1

((1,540,907/1,143,989)^(1/3)) - 1

= -472,467 which is obviously false

or even just:

[Count Latest]/[Count 3 Years Prior]

= 1,540,907/1,143,989

= 0 which is also obviously false

What gives?

Notes

* Count is an attribute with value of 1 for each row

* I have no filters or anything on my Tableau Worksheet, simply the Calculated Fields dragged into the middle of the sheet to view the results

• ###### 1. Re: Sum measure from latest date, and from 3 years prior calculations make no sense

It will be better if you can explain with a sample workbook using superstore data as we are usually lost from Top to Bottom in a long description without workbook

• ###### 2. Re: Sum measure from latest date, and from 3 years prior calculations make no sense

Hi Nick

try   aggregating the values           ((sum([Count Latest])/sum([Count 3 Years Prior]))^(1/3)) - 1

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Sum measure from latest date, and from 3 years prior calculations make no sense

So simple... Thanks a million Jim!