Issues With Percentage Difference Calculation Between Two Columns
Alan Gutierrez Nov 13, 2018 11:04 PMHello 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
[ELECTRICIDAD Eventual/Fixed]
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.

KPI_OPEX_HIST.xlsx 17.3 MB

DIESEL_KPI_OPEXFORUM.twb 437.3 KB