4 Replies Latest reply on Nov 17, 2016 1:24 AM by Adarsh Srivastava

# Create a calculate field based on comparision of values

Hi,

I have some data like :

NameMarks
aaa50
bbb40
ccc65
ddd45
eee55
fff85
ggg

90

I want to create a calculated field considering marks of eee as benchmark such that it can give the results on comparing data like this :

If Marks > marks of eee then 1 as output else 0. The output would look like this

NameMarks
Calculated field
aaa500
bbb400
ccc651
ddd450
eee550
fff851
ggg

90

1

I dont want to hard code the values.

• ###### 1. Re: Create a calculate field based on comparision of values

Hi Ardash,

You can create a calculated field using a window_sum table calculation to get the comparison between the marks of eee and the other marks.

Calculation 1:

IF ATTR([Name]) = 'eee' THEN SUM([Marks]) END

//isolates the mark value for  'eee'

Calculation 2:

WINDOW_MAX([Calculation1])

//uses window sum to repeat the mark value for 'eee' for each name row

Calculation 3:

IF SUM([Marks]) > [Calculation2] THEN  1 ELSE 0 END

//compares the mark value for 'eee' to other name values

I attached a sample workbook that uses these calculations and the sample data you provided. Let me know if this works for you!

Sarah

1 of 1 people found this helpful
• ###### 2. Re: Create a calculate field based on comparision of values

Hello

I have solved your problem and i am sending an attachment containing the solution.

• ###### 3. Re: Create a calculate field based on comparision of values

Hope this is what your looking for.

Please mark it as helpful if it helps you anyway or other.

Regards,

Sudhakar Reddy

1 of 1 people found this helpful
• ###### 4. Re: Create a calculate field based on comparision of values

Thank You Sudhakar. It works for me.