7 Replies Latest reply on May 7, 2018 3:15 AM by harald.solhaug

    Calculating percent differences from a variable number of measurements

    harald.solhaug

      Hello!

      I have a calculation here that I've having trouble figuring out. Here's my problem:

      I have data going back two years. For year one, 8 measurements were taken of a process, and for year two, 10 measurements were taken of a process.

      I have a script that calculates the percent spread in this data (% difference between highest and lowest measurement).

       

      My current script takes all 10 measurements into account, but this obviously doesn't work if I only have 8 active measurements, as it tries to average zero values (or is there a way to solve this?)

       

      Another option is to have two different scripts, and an If/else statement: "If 8 measurements, use script 1 meant for 8 measurements, if 10 measurements, use script 2 meant for 10 measurements".

       

      I've attached a text file with my script for 8 measurements (which itself is kind of messy due to Tableaus very limited MIN/MAX functions)

       

      Thanks for any help!

        • 1. Re: Calculating percent differences from a variable number of measurements
          Jeevan Krishna

          Hi Harold

           

          The issue with avg can be avoided if you change your zeros to NULLS.

           

          You may do it - either in the data or in the tableau.

           

          From data, you can remove the contents from the measurements where they are currently marked as zero.

           

          Inside tableau, since if else statements doesn't allow the use of NULL in their results, we can use this small trick.

           

          Calculate the inverse of the field you are using are values that are to be aggregated.

           

          This removes zeros and makes them null and assumes the same on further calculations

          This reverses your numbers back to normal and replaces zeros with Nulls.you can now use this field for any aggregations.

          • 2. Re: Calculating percent differences from a variable number of measurements
            harald.solhaug

            Hi, thanks for your answer.

             

            My data is already cleaned of any zero values. When I look at the data in Tableau, it reads the missing values as "null"

            I'm not sure if you had a look at the script I included, but it doesn't have any "avg" calculations.

            When I use the calculation for 10 measurements, this is what the graph looks like from before and after the 10 measurements were introduced:

             

            aaaaaaaaaaaaaaaaaaaaaaaaaaa.PNG

            • 3. Re: Calculating percent differences from a variable number of measurements
              Jennifer VonHagel

              Hi Harold,

               

              It would help to understand more about your data itself. Are you using a data source for which you have the option to pivot the separate measure columns to create two columns: effectively Metric Description and Metric Value? Then you can simply take max([metric value]) - min([metric value]) for given dimensions.

               

              Can you attach a .twbx of your data, or similar mocked-up data if your data is confidential? This will help folks in the community give relevant suggestions.

               

              For instance, if your data is something like this:

              And if you are using Excel as data source, you can pivot it in Tableau to make it like this:

               

              And then you can easily calculate all kinds of descriptive stats on your metrics, including % dif.

               

              Best,

              Jennifer

              • 4. Re: Calculating percent differences from a variable number of measurements
                harald.solhaug

                Hi, thank you!

                 

                I'm not sure if I can use pivot tables, but I'll look into it.

                Here's an example of my data. As you can see, the measures apply to specific "Setup numbers".

                I want to find the % difference between the highest and lowest measurements, for each individual Setup Number. The value for each Setup Number becomes one dot in the scatter plot.

                The problem comes when there are no values for Measure 9 & 10.

                 

                DateSetup no.Measure1Measure2Measure3Measure4Measure5Measure6Measure7Measure8Measure9Measure10
                01.01.2016345221712151311121412
                05.03.20163475391598915915
                08.05.2016235671613171616131613
                11.07.201653432898148989814
                13.09.201653256141314914131413149
                16.11.20164467891413159149141315
                • 5. Re: Calculating percent differences from a variable number of measurements
                  Jennifer VonHagel

                  I see. I think pivoting you measures is your best bet - if you have a data source that can be pivoted. To check, in Tableau's Data Pane, select the ten measures, click the little arrow, and see if you have the option to Pivot.

                  If so, then your data looks like this:

                   

                  I think the data will be easier to work with in every respect. You can create your % DIF calculation like this.

                  And your scatter plot (not sure what the x-axis should be):

                   

                  Hope this helps ,

                  Jennifer

                  • 6. Re: Calculating percent differences from a variable number of measurements
                    Jennifer VonHagel

                    If that isn't an option, you can handle the nulls in your calculation as you have it now with an IF statement as you originally suggested.  Here is your current calculation with measures 9 and 10 added to the logic. I can more clearly see your issue here, that nulls in M9 and M10 cause a null result for % Dif.

                     

                    And with an IF statement... It's just so very specific to handling only if nulls are found in measures 9 and 10, it won't handle any other null situation that might arise.

                     

                    This assumes that if Measure9 is null, measure10 is also null.

                    Workbook is attached.

                     

                    Best,

                    Jennifer

                    • 7. Re: Calculating percent differences from a variable number of measurements
                      harald.solhaug

                      Thank you Jennifer!

                      The solution with the IF statement worked perfectly!