2 Replies Latest reply on Nov 14, 2018 8:12 AM by Alan Gutierrez

# Issues With Percentage Difference Calculation Between Two Columns

Hello everyone, I hope having a nice day guys.

Issue Descrpition:

I've a rought problem with a percentage difference calculation between two columns that I had to create by usign a calculated field due to the analysis envyroment.

This percentage difference calculation is necessary to show up a comparation between two site types an its analysis on Diesel an Electricity expenses.

Context:

Diesel Chart:

Fixed Sites: A site that have 4 or more Diesel registers on the data

Eventual Sites: A site that have less than 4 Diesel registers on the data

Electricity Chart:

Fixed Sites: A site that have 4 or more Diesel registers on the data and also 1 or more Eelectricity registers on the data

Eventual Sites: A site that have only electricity registers.

Development:

To get fixed and eventual site classification in every chart I used the next formules (they works right)

[DIESEL Eventual/Fixed]

IF {FIXED [Id Sitio]:COUNTD(IF [Sub Cost] = "Diesel" THEN [Period] END)} >= 4 AND {FIXED [Id Sitio]:COUNTD(IF [Sub Cost] = "Diesel" THEN [Period] END)}=1+DATEDIFF('month',{FIXED [Id Sitio]:MIN(IF [Sub Cost] = "Diesel" THEN [Period] END)},{FIXED [Id Sitio]:MAX(IF [Sub Cost] = "Diesel" THEN [Period] END)})THEN "FIXED" ELSE "EVENTUAL" END

IF {FIXED [Id Sitio]:COUNTD(IF[Sub Cost]="Electricidad" THEN [Period] END)}=1 AND {FIXED [Id Sitio]:COUNTD(IF[Sub Cost]="Diesel" THEN [Period] END)}>=4 AND {FIXED [Id Sitio]:COUNTD(IF [Sub Cost]="Electricidad" THEN [Period]END)}=1+DATEDIFF('month',{FIXED [Id Sitio]:MIN(IF [Sub Cost]="Electricidad" THEN [Period] END)},{ FIXED [Id Sitio]:MAX(IF [Sub Cost]="Electricidad" THEN [Period] END)}) THEN "FIXED" ELSE "EVENTUAL"END

Percentage Difference Calculation Problem:

To get the percentage I used next formule (on both cases, Diesel and Electricity sites)

First with  Lookup property:

LOOKUP(SUM([Cost]),LAST())/LOOKUP(SUM([Cost]),0)

then:

WINDOW_MAX(IF FIRST()=0 THEN IFNULL([% Diesel Lookup],0) END)

that was the only way that I found to get a percentage difference calculation between "FIXED" and "EVENTUAL"  but the percentage value is all wrong. In the Diesel chart case the value necessary should be Fixed Sites vs Eventual Sites. I mean: \$9.23 K USD/\$9.83 K USD =1.06 (100% aprox), but instead I got 13% I don't know what is wrong with that.

I hope someone can give me a hand with this. Thanks for reading.

By the way I couldn't attached the package file due to is off of the forum boundaries, so I added the input file and the original tableau file.

• ###### 1. Re: Issues With Percentage Difference Calculation Between Two Columns

when you have 9.23 and 9.83 fields displaying ... in your actual calculation field write 9.23 field / 9.83 field and display, so that it will display if you format it as percentage... your actual result should be 93.90%

¿?