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

# Calculation for Percentage Difference is not working

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?

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

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

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.

• ###### 3. Re: Calculation for Percentage Difference is not working

PFA is the datasource i have used.

• ###### 4. Re: Calculation for Percentage Difference is not working

Could someone help me on this?

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

HI Vimal

In this  case, you  can use Blend probably.

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

Thnaks,

Shin

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

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

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

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

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

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

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

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

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.