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

    nick.jordan.0

      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