4 Replies Latest reply on Jun 25, 2015 5:23 AM by Rody Zakovich

    Custom date dimension from min date to max date

    Leo Kaplun

      I want to create a custom date dimension in Tableau. I was to take the min([date]) and max([date]) and create every date between those days. In the past I would use a loop, but its not available in Tableau.

      I thought about just creating date table in excel and importing it, but I need it to be dynamic. For example, when I use the date as filter I want to show only certain years and not all the years in the DimDate table.

        • 1. Re: Custom date dimension from min date to max date
          Tableau kumar

          Hi,

           

          If you mock-up your situation using Sample Database, it will help us to understand your situation.

           

          I think the following syntaxes help you, if you want to compare the Date values against Max or Min of Whole Report..

          1) Min Date:

          Window_Min(Min([Date]]

           

          2)  Max Date:

          Window_Max(Max([Date]]

           

          3) Actual Expected

          if Min([Date] <= [Min Date] and   Max([Date]) = [Max Date] then sum(Sales]) end

           

          You have to follow Partition & Addressing carefully while doing Table Calculations.

          ======================

          If you dont want to compare & get the Max or Mon Dates,


          if attr([Date]) <= Max([Date]) and attr([Date]) >= Min([Date]) then sum([Sales]) end


          =============

          Filtering data - Showing selected Years

          Drag the Date field into Work Area, then right click on Date field in the Work area, then click on "Show Quick Filter".It will show the data for selected years only on the view.

          | Tableau Public

           

          Best Regards

          Laxman Kumar

          • 2. Re: Custom date dimension from min date to max date
            Jaideep Rokade

            Hello,

            Create two parameters, From Date and To Date

            create calculated field

             

            IF [DATE]>=[FROM DATE]

            AND [DATE]<=[TO DATE] THEN [measures] END


            HTH!


            Regards

            Jaideep

            • 3. Re: Custom date dimension from min date to max date
              Tableau kumar

              Hi Jaideep,

               

              We have to use "ATTR" function for  Date Field, Like below or as i posted in the above post which was highlighted by Blue color.

               

              IF attr([DATE])>=[FROM DATE]

              AND attr( [DATE])<=[TO DATE] THEN [measures] END


              Best Regards

              Laxman Kumar

              • 4. Re: Custom date dimension from min date to max date
                Rody Zakovich

                Hello Leo,

                 

                With 9.0 we can use LoDs to create row level Min and Max dates. This will give you more flexibility, because you won't need to wrap ATTR around the dates

                 

                IF [DATE] >= { MIN(DATE) }

                AND [DATE] <= { MAX([DATE]) }

                THEN [Measures] END

                 

                Make sure you wrap the Min and Max statement with { }. This is the syntax for Tableau's LoD.

                 

                { MIN([DATE]) } is the same as {FIXED : MIN([DATE]) }

                 

                Hope this helps.

                 

                Regards,

                Rody

                1 of 1 people found this helpful