3 Replies Latest reply on Oct 5, 2016 1:01 PM by Shinichiro Murakami

    Groups and Calculated Fields

    Brittney Parker

      Hi All,

        I have never written SQL queries and am extremely new to Tableau...like a few days new.

       

      I have done some googling and have found a lot of helpful tips as I try creating graphs and whatnot for my dashboard. I have been unable to figure out how to use a group within a calculated field that gets me what I'm needing.

       

      Here is my dilemma :

       

      I have two dimensions: Booked Date and Shipped Date. Both dimensions house dates ranging from 2011 - present.

       

      I have already created groups for each dimension(Booked Date and Shipped Date):

           dates ranging from 1.1.11 - 12.21.15 (titled Previous Dates) in one group and

           dates ranging from 1.1.16-10.5.16 (titled YTD 2016 Booked/ YTD 2016 Shipped) in another

      So two groups per dimension.

       

      I want the count of each: 'YTD 2016 Booked' and 'YTD 2016 Shipped'.

      I've tried creating a calculated field to produce the count of each of these groups titled 'Count of Booked' and 'Count of Shipped' using the following: Count([Booked Date(group)]='YTD 2016 Booked') and the same for Shipped Date (changing up Booked for Shipped) but it's still giving the count of both groups within that dimension.

       

      Then I would like to get the difference of the 'YTD 2016 Booked' count - the 'YTD 2016 Shipped' count as a measure on it's own.

       

      Is this even possible? Should I not even be using groups?

       

      I have attached a workbook of what I'm working with.

       

      Any help would be extremely appreciated.

       

      Thanks,

      Brittney

        • 1. Re: Groups and Calculated Fields
          Shinichiro Murakami

          Hi, Brittney

           

          I recommend first that you should convert your str type date data into date type data.

          Then don't use "Grouping" because it is really trouble some to maintain.

           

          [Booked Date Converted]

          date(DATEPARSE("d/m/yyyy",[Booked Date]))

           

          [Shipped Date Converted]

          date(DATEPARSE("d/m/yyyy",[Shipped Date]))

           

          [Book Date Group]

          if

          [Booked Date Converted]< date("2011/1/1")  then "Book-Old"

          elseif [Booked Date Converted]>= date("2011/1/1") and [Booked Date Converted]<= date("2015/12/21")

              then "Book-Previous"

          elseif  [Booked Date Converted] >= date("2016/1/1")

          then "Book-YTD2016"

          else "Book-Not categorized"

          end

           

          [Ship Date Group]

          if

          [Shipped Date Converted]< date("2011/1/1")  then "Ship-Old"

          elseif [Shipped Date Converted]>= date("2011/1/1") and [Shipped Date Converted]<= date("2015/12/21")

              then "Ship-Previous"

          elseif  [Shipped Date Converted] >= date("2016/1/1")

          then "Ship-YTD2016"

          else "Ship-Not categorized"

          end

           

          [Required Calc]

          sum(if [Book Date Group]="Book-YTD2016" and [Ship Date Group]="Ship-YTD2016" then [Number of Records] end)

           

           

           

          Thanks,

          Shin

          • 2. Re: Groups and Calculated Fields
            Brittney Parker

            Thank you so much, Shin!

             

            I was able to recreate your calculations and get the information I needed.

             

            I was actually wanting just the sum of Book-YTD2016 and then the sum of Ship-YTD2016, which I used your calculation

                 sum(if [Book Date Group]="Book-YTD2016" and [Ship Date Group]="Ship-YTD2016" then [Number of Records] end)

             

            but changed it slightly to get the sums I needed:

            sum(if[Book Date Group]="Book-YTD2016" then [Number of Records] end)

            and

            sum(if[Ship Date Group]="Ship-YTD2016" then [Number of Records] end)

             

            then I was able to get the difference of "Book-YTD2016" minus "Ship-YTD2016"

             

            Again, thank you! I wouldn't have been able to get to where I needed to be without your help in creating those groups using a calculated field!

             

            -Brittney

            • 3. Re: Groups and Calculated Fields
              Shinichiro Murakami

              I'm happy to help you.

              Everybody goes through that stage to learn the best method, don't worry at all and enjoy the community.

               

               

              Thanks,

              Shin