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

# Calculated Field query

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?

 Name Revenue Peter 100 John 200 Tima 50 York 60 Peter,John 200
• ###### 1. Re: Calculated Field query

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

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

 john 200+100=300 peter 100+100=200 tima 50 york 60

Please let me know if this is possible