12 Replies Latest reply on Jun 8, 2018 3:04 AM by Vimal Annamalai

    Calculation for Percentage Difference is not working

    Vimal Annamalai

      I'm trying percentage difference for two different worksheets values in  %diff worksheet. Below are the precise information about issue & also attached my sample workbook too.:-

       

       

      Common Filters for both Sheet  -  TestName, Model

      Applicable for Sheet 1 alone      -  Branch1, Build1, TestDate1

      Applicable for Sheet 2 alone      -  Branch2, Build2, TestDate2

       

      Cal_Run1 & Cal_Run2 both are calculated field with below code.

       

      Cal_Run1:-

       

      IF (([Test Name] = [TestName Filter]) AND  ([Model] = [Model Filter]) and([Branch] = [Branch1]) and([Build]= [Build1]) and ([Test Date] = [Test Date1])) THEN

      [Run Value]

      END

       

      Cal_Run2:-

       

      IF (([Test Name] = [TestName Filter]) AND  ([Model] = [Model Filter]) and([Branch] = [Branch2]) and([Build]= [Build2]) and ([Test Date] = [Test Date2])) THEN

      [Run Value]

      END

       

       

      Sheet 1  Value:

       

       

      Sheet 2 Value:

      %difference Sheet -  Trying to calculate percentage difference between Cal_Run1 & Cal_Run2 in this sheet.

       

       

       

      Formula used to calculate percentage difference is below:-

       

      %diff :-

       

      IFNULL((([Cal_Run1] - [Cal_Run2])/(([Cal_Run1] + [Cal_Run2])/2))*100,0)

       

      Important Note : - Percentage Difference should be calculated for Each & Every Interval

       

       

       

      Can some one help me on this?

       

      Thanks in Advance.

        • 1. Re: Calculation for Percentage Difference is not working
          Anupam Mehta

          Hi Vimal,

           

          As we can see in attached sample report both sheets are different and using different filters which are not used in third sheet so its returning same data for cal 1 and cal 2. Hence as values are same ([Cal_Run1] - [Cal_Run2]) returning zero.

           

          please fix this part it will return desired output.

           

          Regards,

          Anupam

          • 2. Re: Calculation for Percentage Difference is not working
            Vimal Annamalai

            Hi Anupam Mehta,

             

            Thanks for quick response.

             

            As you said, If i apply filters of both sheet 1 &2  to %Difference Sheet, No values will not be displayed because

            That sheet will search data for all filters matches in a single row.

             

            Correct me if i'm wrong.

            • 5. Re: Calculation for Percentage Difference is not working
              Shinichiro Murakami

              HI Vimal

               

              In this  case, you  can use Blend probably.

               

               

              Link only with Interval ID.

               

              And put filter as you like from respective data source's field..

               

               

              Thnaks,

              Shin

              • 6. Re: Calculation for Percentage Difference is not working
                Vimal Annamalai

                HI Shinichiro Murakami,

                 

                Thanks very much for your help. This works perfectly but only one more concern i have, like i need to cascade the filters in the below way.

                 

                TestName , Model  -  Common Filters for both Blend 1&2 Datasources

                Branch as Branch1, Build as Build1, TestDate as TestDate1 - From Blend1 Datasource

                Branch as Branch2, Build as Build2, TestDate as TestDate2 - From Blend2 Datasource

                 

                 

                Cascading Hierarchy is  like Below:-

                 

                Common Filters for both Sets:-

                TestName      - Parent Filter

                Model            -  Child of TestName( Should have Relevant values  based on selected value in TestName )

                 

                Set 1 Filters

                Branch1        -  Should have Relevant values  based on selected values of TestName & Model

                Build1            -  Should have Relevant values  based on selected values of TestName, Model, Branch1

                TestDate1      -  Should have Relevant values  based on selected values of TestName, Model, Branch1, Build1

                 

                 

                Set 2 Filters

                Branch2        -  Should have Relevant values  based on selected values of TestName, Model

                Build2            -  Should have Relevant values  based on selected values of TestName, Model, Branch2

                TestDate2      -  Should have Relevant values  based on selected values of TestName, Model, Branch2, Build2

                 

                 

                My Triage:-

                 

                I tried in below ways but not works for me very well.

                 

                1) Kept Testname as Context Filter & enabled the "All Values in Context" for Model. Then enabled "Only Relevant values" for all set1 filters but the same option is not available for Blend2(set Filters) datasource filters.

                 

                2) Created Calculated field like below for each & every filters with  IF condition but not works very well as expected.

                 

                 

                TestName Filter:-

                [TestName]

                 

                 

                Model Filter:-

                 

                If ([TestName] = [TestName Filter]) Then

                [Model]

                END

                 

                Branch1:-

                 

                IF (([TestName] = [TestName Filter]) AND  ([Model] = [Model Filter])) THEN

                [Branch]

                END

                 

                Branch2:-

                 

                IF (([TestName] = [TestName Filter]) AND  ([Model] = [Model Filter])) THEN

                [Branch]

                END

                 

                Build1:-

                IF (([TestName] = [TestName Filter]) AND  ([Model] = [Model Filter]) and ([Branch] = [Branch1])) THEN

                [Build]

                END

                 

                Build2:-

                IF (([TestName] = [TestName Filter]) AND  ([Model] = [Model Filter]) and ([Branch] = [Branch2])) THEN

                [Build]

                END

                 

                TestDate1:-

                 

                IF (([TestName] = [TestName Filter]) AND  ([Model] = [Model Filter]) and ([Branch] = [Branch1]) and ([Build] = [Build1]))THEN

                [Test Date]

                END

                 

                TestDate2:-

                 

                IF (([TestName] = [TestName Filter]) AND  ([Model] = [Model Filter]) and ([Branch] = [Branch2]) and ([Build] = [Build2]))THEN

                [Test Date]

                END

                 

                 

                Is there any way possible to achieve my requirement?

                 

                 

                Attached Sheet with some modifications please kindly have a look.. Your help is most expected .

                 

                I have also attached the sheet with my second triage (Cal_cascdingfilters_SM_10.4.twbx)

                • 7. Re: Calculation for Percentage Difference is not working
                  Shinichiro Murakami

                  Hi Vimal,

                   

                  It's difficult to understand the behavior of filter with given data set. It only has one combination.

                   

                   

                  Anyways, "Only relevant value" is only available in same data source and blend's case it does not work.

                  Also Tableau does not fully support cascading filters and not sure you get satisfying answer or not even apart from multiple data source portion.

                  I mean cascading filter is not supported even on single data source and under-layer filter value will hide upper-layer value in case you do not reset filter with selecting all.

                   

                  Tnanks,

                  Shin

                  • 8. Re: Calculation for Percentage Difference is not working
                    Vimal Annamalai

                    Thanks much much for the quick response.

                     

                    So is there no way to implement my requirement?

                     

                    Instead of blending the data, do we have any way to achieve the cascading filters & Percentage difference?

                    • 9. Re: Calculation for Percentage Difference is not working
                      Mavis Liu

                      Hi Vimal.

                       

                      The only way I can think of is by using a blend or parameters. Because your worksheets have separate filters which are driving the values, you can't bring in some but isolate the others when you try to calculate the % difference in the same worksheet.

                       

                      Thanks,

                       

                      Mavis

                      • 10. Re: Calculation for Percentage Difference is not working
                        Vimal Annamalai

                        Hi Mavis,

                         

                        I have tried blending the data sources  & tried with parameter also. Unfortunately both of  the ways are not allowing me to cascading the filters.

                        • 11. Re: Calculation for Percentage Difference is not working
                          Mavis Liu

                          Hi Vimal,

                           

                          I don't think you'll be able to cascade the filters in the way you want it to. You'll need to have the data in the same data source and you currently cannot do that if you want your worksheet to ignore/accept some filters to find the % difference.

                           

                          Thanks,

                           

                          Mavis

                          • 12. Re: Calculation for Percentage Difference is not working
                            Vimal Annamalai

                            Hi Mavis,

                             

                            One final question,

                             

                            Let we assume i'm having only one datasource and created two set of filters.

                             

                            For sheet 1 -  I mapped set1 filters  for Run_value1 calculated field

                            For sheet2 -   I mapped set2 filters for Run_value2  calculated field  like i mentioned above.

                             

                             

                            Now i'm trying to calculate percentage difference in Sheet3 for Run_value1 &2.

                            In this case i'm getting sum of values (like it is doing sum of all interval 1 values, Interval 2 values and so on ) but it is not displaying corresponding % difference value to the Interval ID.

                             

                            What can i do to display the percentage difference value as it is for corresponding Interval ID with doing any aggregation operations like sum?

                             

                             

                            Thanks a lot to listening my request.