2 Replies Latest reply on Nov 12, 2018 6:56 AM by Alexander Mik

    Find every year in From To date field

    Alexander Mik

      Hi all,


      I have a data set which looks like this:


      Product          Price          Valid from          Valid to

      A                        10            1-3-2014          1-4-2016  

      A                        12            2-4-2016           12-3-2018

      A                        13            13-3-2018          -


      What I need is this:


      Product          Price          Year

      A                    10               2014

      A                    10               2015

      A                    12               2016

      A                    12               2017

      A                    13               2018


      How can I achieve this? I already tried it with prep but can't get the answer. Hope you guys can help me.


      Best regards,


        • 1. Re: Find every year in From To date field
          Ankit Bansal



          You can simply create a table with all the years listed like:









          Then join your original dataset with this table with below condition


          year_column >=year(valid from) and  year_column <=ifnull(year(valid To),year(valid from))


          You can use tableau's create join calculation option to derive the right hand side columns in above expression.


          Also if your data source in not a database but its a file then you will not be able to do >= and <= joins. In that case just join 2 dataset on 1=1 and then create a calculation column as above expression, put it on filter shelf and choose TRUE.


          Hope it helps.




          • 2. Re: Find every year in From To date field
            Alexander Mik

            Great that worked! I justed the 1=1 method in Tableau Prep