3 Replies Latest reply on Oct 30, 2018 9:40 PM by Ankit Bansal

    Current Semester

    dinesh punnam

      Hello Tableau Community,

       

      Q1+ Q2 = Semester 1

      Q3 + Q4 = Semester 2

       

      I want to calculate  Current year (2018)Current semester sales and Previous year(2017) Same semester sales. How can we write a formula dynamically so that It picks current semester (Q3+Q4) based on today's date?

       

      As per today we are in 4th Quarter in this case current semester would be (Semester 2)  --   I am trying to achieve this based on today date , please help me in this regards

       

      Thanks

      Dinesh

        • 1. Re: Current Semester
          Zhouyi Zhang

          Hi, Dinesh

           

          Please try below calculation

           

          IF DATEPART('quarter',TODAY()) = 3 OR DATEPART('quarter',TODAY()) = 4 THEN

              'Semester 2'

          ELSE

              'Semester 1'

          END

           

          ZZ

          • 2. Re: Current Semester
            dinesh punnam

            ZZ,

             

            I wrote that calculation already and as it is a Boolean , how can i use to capture the current semester and

            how can i use that in  calculating the Current semester sales and previous year same semester sales?

             

            i.e 2018 Semester 2 sales and 2017 semester 2 sales --   It has to be dynamically change the calcs with change in date.

             

            Ex:

             

            IF I put above in filter and select semester 2 for now - it will not show any data from jan1st as it will be semester 1

             

            please suggest

            • 3. Re: Current Semester
              Ankit Bansal

              Dinesh create 2 calculated field for semesters

               

              current Semester:

              IF DATEPART('quarter',TODAY()) = 3 OR DATEPART('quarter',TODAY()) = 4 THEN

                  'Semester 2'

              ELSE

                  'Semester 1'

              END

               

              order date semester:

               

              IF DATEPART('quarter',[order date]) = 3 OR DATEPART('quarter',[order date]) = 4 THEN

                  'Semester 2'

              ELSE

                  'Semester 1'

              END

               

              Then create calculated field for sales as :

              sales_current_semester_current_and_previous_year:

               

              if [order date semester]=[current Semester] and (year([order date]=year(Today()) or  year([order date]=year(Today())-1)  then [sales] end.

               

              Thanks,

              AB