4 Replies Latest reply on Apr 6, 2016 10:23 AM by Maria Alex

    Percentage Difference calculation

    Maria Alex

      Attaching the tableau workbook .

       

       

      I need to be able to find the percentage difference for each category comparing 2016 over 2015 by the week.

       

      Difference of percentage by the week for each Rate Type ( comparing b/w 2015 and 2016)

      Example : Week 1

      EPR : 13.79 - 27.45  = -13.66%

      ESR : 24.14 - 15.69  = 8.45%

      Negotiated : 34.48 - 25.49 = 8.99%

      Published :   27.59 -
      31.37 = -3.78%


      I actually built 2 views in the workbook – any view would work as long as I can embed the calculation in the report or even a completely new report will also work .

       
       
      Thanks

      Maria
       
       
       
       
       
       
       
       
       


       

       

       

       

        • 1. Re: Percentage Difference calculation
          Shinichiro Murakami

          Maria,

           

          Because you are using 8.3, this kind of calculation is pretty tough as a initial note.

          From 9.0 onward, there is LOD calculation which makes this calc incredible smooth.  // Overview: Level of Detail Expressions

           

          Anyways, here is a solution with 8.3

           

          Create Year and Week field /// to link two data later

           

          [Year Calc]

          year([Issuedate])

           

          [Week Calc]

          datepart('week',[Issuedate])

           

          Duplicate date.

           

          2nd data is used to calculate aggregated value across Rate Type.

           

           

          Link Two data only using Year x Week

           

          [Peercentage of TTL]

          sum([HotelTrx])/SUM([CustomSQLQuery (HotelProduct) (copy)].[HotelTrx])

           

          Put above field in measure, and put one more with table calc.

           

           

           

           

           

          Thanks,

          Shin

           

          8.3 attached

          1 of 1 people found this helpful
          • 2. Re: Percentage Difference calculation
            Maria Alex

            Thanks Shin - super helpful . Will share the resolution with my team. Quick Question - the first step was to duplicate the data source which means I need to refresh both the extracts. The dataset I have brings in about 6million records - so it won't be feasiable to refresh 2 datasets and the size of the workbook will be twice large , right ?

            • 3. Re: Percentage Difference calculation
              Shinichiro Murakami

              I don't think so.

              I don't know the database side very well, but duplication does not double the data size, I think.

              The reason I think so is I didn't feel any speed issue after duplicating big data set.

               

              I don't know exactly about technical background, but the duplication is using same dataset and access differently??

              Means no speed, size concerns.

               

              Again this is my experienced-basis opinion and no technical background.

              I will post the question.

               

              Thanks,

              Shin

              • 4. Re: Percentage Difference calculation
                Maria Alex

                hi Shin,

                I have 9.2 version installed. you mentioned in your reply earlier

                From 9.0 onward, there is LOD calculation which makes this calc incredible smooth.  // Overview: Level of Detail Expressions

                 

                Went thru the LOD calculations but quite didn't figure out. Could you help me with the solution in 9.2?

                 

                Thanks

                Maria