1 Reply Latest reply on Nov 8, 2018 5:57 PM by swaroop.gantela

    How could I solve an addition problem by using calculated fields?

    Alan Gutierrez

      Description:

      I'm Developing an analysis to get the number of "Fixed" sites and also "Eventual" sites on a network company.

       

      Fixed Site: When a kind of site has 4 or more records of Diesel and also if it have 1 or more records of Electricity

      Eventual Site: Only when a site has a electricity records

       

      I use 5 fields to my Input to get the right filters.

      Sub Cost: Electricity and Diesel

      Technology: Could be LTE, 3G, LTE/3G, etc

      Cost: Amount

      Id Site: Site Name

      Period: Date

       

       

      Problem:

       

      "Electricidad Eventual" site amounts should be added if I remove the "Id Sitio" field of the rows zone, due to they owns to the same "Tech Installed" (LTE/3G)

      1.PNG

      But Instead, the amounts are added in the same column ("Electricidad Fixed")

      2.PNG

       

      Formulas and the procedure that I followed:

       

      1. I created this calculed field to can clasify between "Eventual" and "Fixed" Site as I said in the top.

      [Electricidad_Eventual/Fixed)

      IF COUNT([Sub Cost] = "Electricidad") >= 1 AND COUNT([Sub Cost] = "Diesel") >= 4 THEN "FIJO" ELSE "EVENTUAL" END

      Electricity.PNG

       

      2.Then, I made a calculated field to can sum the sites money amount.

      [SUM ELECTRICIDAD]

      SUM([Cost])

      SUM.PNG

      3. Finally, I created two calculated fields. Due to I needed to add the money amount in the Fixed Sites Category and also to add the money amount in the Eventual Sites Category

      [Electricidad Fixed)

      IF [Electricidad_Eventual/Fixed] = "FIJO" THEN [SUM ELECTRICIDAD] ELSE NULL END

      ElectricityFixed.PNG

      [Electricidad Eventual]

      IF [Electricidad_Eventual/Fixed] = "EVENTUAL" THEN [SUM ELECTRICIDAD] ELSE NULL END

       

      ElectricityEventual.PNG

      I don't know why the amounts can be added in the right category when I remove the "Id Sitio" Field. Also I've tried with FIXED function but it didn't works. I hope you cant help me.

      I attached the files.