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

    Issues With Percentage Difference Calculation Between Two Columns

    Alan Gutierrez

      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

      [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.

       

      wrong.PNG

       

      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.