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

# Calculating percent differences from a variable number of measurements

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

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

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:

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

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

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.

 Date Setup no. Measure1 Measure2 Measure3 Measure4 Measure5 Measure6 Measure7 Measure8 Measure9 Measure10 01.01.2016 34522 17 12 15 13 11 12 14 12 05.03.2016 34753 9 15 9 8 9 15 9 15 08.05.2016 23567 16 13 17 16 16 13 16 13 11.07.2016 53432 8 9 8 14 8 9 8 9 8 14 13.09.2016 53256 14 13 14 9 14 13 14 13 14 9 16.11.2016 44678 9 14 13 15 9 14 9 14 13 15
• ###### 5. Re: Calculating percent differences from a variable number of measurements

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

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

Thank you Jennifer!

The solution with the IF statement worked perfectly!