5 Replies Latest reply on Jul 26, 2017 6:48 AM by Jason Kleinhans

    DateTrunc show END of Quarter

    Jagjit Singh

      Hi All,

       

      I have the below calculated field to show data by month, quarter and year. The calculation works fine but for the quarterly view the values appear in the beginning of the quarter and not end of quarter. Any ideas how i can show quarter end of month?

       

      DATE(CASE [Parameters].[Period]

      WHEN "Day" THEN [Date]

      WHEN "Week" THEN DATETRUNC('week',[Date])

      WHEN "Month" THEN DATETRUNC('month',[Date])

      WHEN "Quarter" THEN DATETRUNC('quarter',[Date])

      WHEN "Year" THEN DATETRUNC('year',DATEADD('month',6,[Date]))

      END)

       

      For example in the view below instead of Jul16 it should show Sep16 and so forth

       

      quarter end date.PNG

       

       

      Thanks

      Jag

        • 1. Re: DateTrunc show END of Quarter
          Sherzodbek Ibragimov

          Have you tried to edit axis, under major tick marks? You can set fixed and for tick origin, you can set a monthly early date, say 6/30/2015 and see if it does a trick.

          • 2. Re: DateTrunc show END of Quarter
            Deepak Rai

            I think this should  work:

            DATE(CASE [Parameters].[Period]

            WHEN "Day" THEN [Let Date]

            WHEN "Week" THEN DATETRUNC('week',[Let Date])

            WHEN "Month" THEN DATETRUNC('month',[Let Date])

            WHEN "Quarter" THEN DATETRUNC('month',DATEADD('month',2,[Let Date]))

            WHEN "Year" THEN DATETRUNC('year',DATEADD('month',6,[Let Date]))

            END)

            Logic is Quarter would always point to Start of Quarter irrespective of the month in the date. So if we add 2 more months then it would return last month of Quarter. I have not checked.

            Hope it Helps!!!

            Thanks

            Deepak

            1 of 1 people found this helpful
            • 3. Re: DateTrunc show END of Quarter
              Jagjit Singh

              Nope that did not work. When Quarter is selected, the axis shows Jul, Oct, Jan which are start of the quarter. I like to show Sep, Dec, Mar, Jun

               

               

               

               

              Thank

              Jag

              • 4. Re: DateTrunc show END of Quarter
                Shinichiro Murakami

                Maybe below??

                 

                 

                DATE(CASE [Parameters].[Period]

                WHEN "Day" THEN [Date]

                WHEN "Week" THEN DATETRUNC('week',[Date])

                WHEN "Month" THEN DATETRUNC('month',[Date])

                WHEN "Quarter" THEN DATETRUNC('quarter',DATEADD('month',3,[Date]))-1

                WHEN "Year" THEN DATETRUNC('year',DATEADD('month',6,[Date]))

                END)

                1 of 1 people found this helpful
                • 5. Re: DateTrunc show END of Quarter
                  Jason Kleinhans

                  I am new to Tableau and also struggling to use DATETRUNC I am trying to compare my sales for Previous and Period year and [Compared By] and [Select a metric] is my parameters , but I get errors , see the screenshot for details of the error , can you please guide me in the right direction , thanks

                   

                  Jason

                   

                  //What we want her is to have Tableau take the user's input (Posting Date)
                  //Calculate the duration of the period that the user wants to use (Compare by)
                  //And calculate the value for the metric that the user has selected (Select a Metric)

                  IF (DATETRUNC([Compare by],[Posting Date])=
                      DATETRUNC([Compare by],[Shipment Date])
                  AND
                  DATETRUNC('day',[Posting Date])<= DATETRUNC('day',[Shipment Date]))

                  THEN
                  [Select a metric]
                  END