4 Replies Latest reply on Apr 13, 2016 9:59 AM by David Guzman

    Calculating YoY % Change Using Concatenation

    David Guzman

      Hi All,

       

      I am trying calculate Year/Year percentage change using a concatenation string. I needed to construct this dimension because my data separated Quarter (a string Value) and Year (an Integer Value) into two dimensions. When I try to the Date Parse Function to convert it to a date, the output is not what I am looking for.

       

      My string is in the form: [Quarter] + "-" + STR([Year])

      and

      My Date Parse Function is DATEPARSE("yyyyQQ", [Concat (Quarter Year)])

       

      Is this the best approach? Or is there another way I can calculate Year/Year change using strings?

      Attached is my Tableau Package.

       

      Thanks in advanced

        • 1. Re: Calculating YoY % Change Using Concatenation
          Shawn Wallwork

          Is this the best approach? Or is there another way I can calculate Year/Year change using strings?

          ['Best' is a loaded word, so I'm going to use 'Better'.]

           

          When working with dates (well almost anything) it is better to avoid strings. Or at least convert them to numeric values as early in the process as possible. This is because all computers/softwares process numbers faster than they process strings (since they have to convert strings to number values before they can 'understand' them).

           

          So here's a 'better' solution to your issue. First we convert your string quarters to month numbers:

           

          Convert Quarter

          CASE [Quarter]

          WHEN 'Q1' THEN 1

          WHEN 'Q2' THEN 4

          WHEN 'Q3' THEN 7

          WHEN  'Q4' THEN 10

          END

           

          Now we can create a proper date:

           

          Make Date

          MAKEDATE([Year],[Convert Quarter],1)

           

          Now you have a date field you can use to do YoY calcs.

           

          --Shawn

           

          PS: If you would have mentioned your version number I would have posted a packaged workbook for you. (I only know you are not using the latest version, 9.3)

           

          --Shawn

          • 2. Re: Calculating YoY % Change Using Concatenation
            David Guzman

            Hi Shawn,

             

            Thank you so much for the "better" solution to this issue. Your step by step instructions really helped me understand your thinking process when constructing this Dimension.

            Next time I will mention what version my package is, but your instructions were good enough as it is.

             

            Really appreciate the help.

            Now I can continue building creative and effective dashboards.

            • 3. Re: Calculating YoY % Change Using Concatenation
              Shawn Wallwork

              Ahh David, these sorts of posts are catnip for us helpers! You both understood the solution I offered, and learned how to help us helpers better help you by always indicating the Tableau version number you are using. Excellent!

               

              I look forward to your future community contributions -- either as a Questioner or a Helper. 'Building creative and effective dashboards' is a goal we all share!

               

              Cheers,

               

              --Shawn

              • 4. Re: Calculating YoY % Change Using Concatenation
                David Guzman

                Hi Shawn,

                 

                I was wondering if you can help me with another matter. I was able to construct a desired bar graph illustrating the share percentage of each item over time. When I drag the visualization into a Dashboard, it doesn't appear as nice as the one in the worksheet. It looks perfect when I display it in the worksheet. Is there anyway i could scale it properly on the dashboard? Is there a better solution to display these bar graphs?

                 

                My Package workbook is version 9.2.

                 

                Thanks,

                David