2 Replies Latest reply: Feb 8, 2017 3:22 PM by pedro juarez RSS

    Problem with conditional & alt result

    pedro juarez

      Hi

       

      I have this formula in one pivot table graph however the last result isn't giving me what I need.

       

      alt(if((FABS(Sum ([UN DEVUELTAS]))/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS])))>2,2,fabs(Sum ([UN DEVUELTAS]))/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS]))),"X")

       

      the basic formula is this: Sum ([UN DEVUELTAS])/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS])

      if this formula give me a result greater than 2, it should report 2

      and if this formula is undetermined (9/0), it should report 1

       

      if i use the formula  with 0 as "X", the original formula and the  first conditional works however the second conditional doesn't work and the others columns of the pivot table works also,

       

      if i use the formula  with 1 as "X", nothing works (the original formula, the  first conditional & the second conditional & all the other columns of the pivot table, i don't know why the are affected by the result of this column)


      what should I change???

       

      the script of my table is like this:

      VENTAS:

      LOAD

           [FECHA DE FACTURA],

           CODIGO,

           [UN VENDIDAS],

           [UN CANCELADAS],

           [UN DEVUELTAS],

       

      FROM

      [C:\Users\Pedro_Juarez\Documents\PMJT\Control de Inventarios México\2015\Global\Ventas\2017.xlsx]

      (ooxml, embedded labels, header is 5 lines, table is HBRIEF_7534);


      thanks for your help

      regards,