2 Replies Latest reply on Oct 10, 2016 1:48 PM by Pragati g

    Calculated Field query

    Pragati g

      Dear All,

       

      I have  to create a calculated field where the data is like this

      Now I want revenue based on names for eg for John it will be (200+200/2)=300.  200/2 is due  to the common revenue Peter, John has

      similarly for Peter it should be (100 + 200/2)=200

       

      How can I achieve this?

      Appreciate your Help Thanks

       

       

      NameRevenue
      Peter100
      John200
      Tima50
      York60
      Peter,John200
        • 1. Re: Calculated Field query
          Sreekanth Kasaraneni

          Hi Pragati,

           

          i have created parameter to select Name in the sheet.

          used different calculations

           

          IF CONTAINS([Name],[Name Parameter]) THEN ([Number of Records]) END          -----  display 1 for filter selected Name

          { FIXED [Name] : SUM(IF LEN([Name]) = LEN([Name Parameter]) AND  CONTAINS([Name],[Name Parameter]) THEN [Revenue] END)}     ----  display first value i.e Revenue of selected Name

          { FIXED [Name] : SUM(IF CONTAINS([Name],[Name Parameter]) THEN [Revenue] END)}  -------------  display total Revenue of selected Name in the entire data

          (SUM([Total_Value]) - SUM([First_Value])) / count([count])  ------------------ display difference value of Revenue

          ATTR([First_Value]) + ([Difference_Value]) ------- display expected revenue of the selected Name

           

           

          please check the attached workbook sheet Result

           

          Thanks,

          Sreekanth.

          • 2. Re: Calculated Field query
            Pragati g

            Hey Sreekanth,

            I tried pulling the calculated fields you have created, below is the snapshot:

             

            It is giving null values,I am looking for something like this

              

            john200+100=300
            peter100+100=200
            tima50
            york60

             

            Please let me know if this is possible