1 Reply Latest reply on Jul 9, 2018 3:13 PM by Wesley Magee

    Age in Years Between Current Year and Product Year Based on Dimension String

    Juan Barnett

      Hello Community!


      I've searched high and low to find an answer to my solution and I've yet to find a good example of what I'm trying to accomplish.


      In my data set I have a list of products (product name), a category of those products (product type), exact date those products were launched (Launch Date) and a measure that captures various product actions (Action Type) that is a string dimension that ranges from 1 to 4 (1 = Minor Change, 2 = Major Change, 3 = Brand New product, 4 = Discontinued Product).


      I'm trying to find the most intuitive way to measure the age of each product primarily where the Action Type = 3. It might also be helpful to include 2 and 3 as Major and All-New tend to have similar market impacts and would likely overlap in many "asks" from our team.


      While I understand the basics behind a simple DATEDIFF Calculated Field, I'm not sure the optimal way to incorporate the Action Type element into the formula to best answer this question:


      how many years has it been since we made an Action Type = 3 for each of these products based on today's year? Keep in mind there could be multiple times that an Action Type 3 has shown for a specific product so this calculation would also need to only select the most recent change.

        • 1. Re: Age in Years Between Current Year and Product Year Based on Dimension String
          Wesley Magee


          I think this calculation will get you what you're looking for:


          DATEDIFF('year', MAX(IF [Action Type] = 3 THEN ([Launch Date]) END), (TODAY()))


          The section in bold is finding all of the records with Action Type 3 and then returning the most recent (max) date. The rest of the calculation is doing the date diff in years.


          This calculation will need to be shown with the Product Name to show the specific number of years for each product.


          If you wanted to show this for Action Type 2 or 3 then it would look like this:


          DATEDIFF('year', MAX(IF [Action Type] = 3 or [Action Type] = 2 THEN ([Launch Date]) END), (TODAY()))


          Let me know if you have any issues.



          If this post 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.