9 Replies Latest reply on Nov 1, 2017 10:06 AM by adama.walters

    Assign dates to fields from 2 columns down (to the right) in hierarchy - please help

    adama.walters

      Hello,

       

      I'm using Tableau 10.3.0. I'm trying to calculate a time between dates, but the dates are farther down the hierarchy than what I'm used to. I've attached a packaged workbook, which is essential to look at to understand what I'm trying to do.

       

      Per each AssetTag, I'm trying to create a field that is the distance between the dates of the two most recent Phases of the AssetTag. The Phases contain Check Point descriptions and Check Point measures (dates). The Phase dates should be calculated from the "Furnishings" Check Point Description checkpoint measure date.

       

      With this date calculated for the Phase, I need to calculate the amount of time between the two most recent Phases, therefore having a field that contains a number for each AssetTag.

       

      So, in the screenshot below the column names from left-right are AssetTag, Phase, Check Point Description, Check Point Measure, and Profit.

       

      For the AssetTag AA-10645, you can see that both the East, South. and West phases have "Furnishings" Check Point Descriptions, so I will only focus on those. I want to assign dates to each phase based on the Check Point Measure for each "Furnishing" Check Point Description. Then, once each phase has dates, I want to calculate which two phases have the most recent dates and calculate the time between those, and assign that to the AssetTag. So in this example, the South and West Phases have the most recent dates for Furnishings. The time between the two most recent phases therefore would be September 4, 2016 - July 4, 2016 =  2 months = 62 days. Is there are way to create a calculated field to show this number per each AssetTag?

       

      Is this possible? I appreciate the help. This is based on Superstore data so it's not real data but the real data situation is similar.