1 Reply Latest reply: Jan 11, 2018 1:50 PM by Sunny Talwar RSS

    Calculate sum of values calculated from an if statement

    Filipe Gomes

      Hi,

       

      I have to calculate a certain value for each [Equipamento-Num. SAP] on my data set using the following if cycle depending on the number of occurrences of [Ordem] for when Avaria={1}:

       

      If (IsNull(Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])) or Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])=0,

        (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6)))),

          (Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} All Data)-MakeDate(2008, 01, 01))*24/pow(10,6)))))


      Then I also have to place what is above inside another cycle for when Avaria={0}:


      If (Avaria=1,

        ##The Code above##,

        (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6))))

      )

       

      This  works fine on a table for when I have the results by [Nome Equipamento]. However I want to have the average of all these values on a certain year. And when I change the table's dimension to years and apply the following expression the results I get do not resemble the results I should have:

       

      Sum(If (Avaria=1,

        If (IsNull(Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])) or Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])=0,

        (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6)))),

          (Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} All Data)-MakeDate(2008, 01, 01))*24/pow(10,6))))),

        (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6))))

      ))

       

       

      How can I get the correct sum of the values I calculate through an if statement?

       

      Thank you,

        • Re: Calculate sum of values calculated from an if statement
          Sunny Talwar

          May be this

           

          Sum(Aggr(

          If(Avaria=1,

            If (IsNull(Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])) or Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])=0,

            (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6)))),

              (Aggr(Count({<Avaria={1}>}[Ordem]), [Equipamento-Num. SAP])/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} All Data)-MakeDate(2008, 01, 01))*24/pow(10,6))))),

            (1/(((Max({<[Factory]=, [Unidade]=, [Nome Equipamento]=, [TAG.Nr]=, [Boundary]=>} Total Data)-MakeDate(2008, 01, 01))*2*24/pow(10,6))))

          )

          , [Nome Equipamento], Year))