7 Replies Latest reply on Sep 9, 2016 6:20 AM by Sandeep Kotha

# Calculation field Problem??

Hi All,

Could someone reply for the below question? I have the below fields.

1.New:  Before,After,No

2.ID   : All ID's .This can have repetition.

3.Rating: 0 to 10:

4.Domain: Yes/No

I have the data in such away that : When Domain=No then will be 'No' in all the fields otherwise same field data it has.Below is sample data:

 Domain ID NEW Rating Yes 25997 Before 1 No No No No No No No No No No No No Yes 34951 After 9 No No No No No No No No No No No No No No No No Yes 17827 After 7 No No No No Yes 39190 Before 10 Yes 75330 Before 3 Yes 70002 Before 6 No No No No No No No No Yes 56249 After 6 No No No No Yes 45386 Before 4 Yes 48165 Before 8 No No No 9 No No No No Yes 5865 After 5

and so on.

What I want:

First I have to select similar ID's who are in both "Before" and "After" levels and then find the below points.

IF

Rating(After) > Rating(before) then 'Positive'

Rating(After) < Rating(before) then 'Negative'

Rating(After) = Rating(before) then 'NoChange'

and Then I have to find the counts and %'s of each status as below:

This is the final output that I want:

 Count % Positive 5 50% NoChange 2 20% Negative 3 30% TOTAL 10 100%

What I have done: I created the three fields as shown below

AfterScore:    If NEW='After' then [Rating] END

BeforeScore: If NEW='Before' then [Rating] END

Status:

if MAX([AfterScore])>max([BeforeScore]) then 'Positive'

ELSEIF MAX([AfterScore])<MAX([BeforeScore]) then 'Negative'

ELSEIF MAX([AfterScore])=MAX([BeforeScore]) then 'No Change' else 'NULL' END

Now, When I drag the field called "Status" on the rows shelf, I got the below output.

It has only one level "No Change". Also, I am unable to find the count(AGG(Status)).

I know I have done some mistake or might be that my approach is wrong. But i am unable to identify my mistake what I have done.

Could someone help me on this issue with the correct code for my output.

This is the final output that I want:

 Count % Positive 5 50% NoChange 2 20% Negative 3 30% TOTAL 10 100%

Thank you.

Best Wishes,

Sandeep

• ###### 1. Re: Calculation field Problem??

You need to aggregate rating to do it.

You can't have no as rating value. It should be numeric.

I improved your sample of data.

 Domain ID NEW Rating Yes 5865 After 5 Yes 17827 After 7 Yes 25997 Before 8 Yes 34951 After 6 Yes 39190 Before 6 Yes 45386 Before 3 Yes 48165 Before 8 Yes 56249 After 6 Yes 70002 Before 5 Yes 75330 Before 7 Yes 5865 Before 1 Yes 17827 Before 7 Yes 25997 After 1 Yes 34951 Before 9 Yes 39190 After 10 Yes 45386 After 4 Yes 48165 After 8 Yes 56249 Before 6 Yes 70002 After 6 Yes 75330 After 3 No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No No 9 No No No No
• ###### 2. Re: Calculation field Problem??

Hi, you have a good line of thought going. One issue is your "max" function is being applied across everything the way you have it now. All your current result is saying is the highest overall "Before" is the same as the highest overall "after" in the set. What happens if you drag ID into the row column before that aggregation?

One way to deal with it is to use an lod to apply it to [ID]. Like...

if  { fixed [ID] : MAX([AfterScore]) } > { fixed [ID] : max([BeforeScore]) } then 'Positive'

ELSEIF { fixed [ID] : MAX([AfterScore]) } < { fixed [ID] : MAX([BeforeScore]) } then 'Negative'

ELSEIF { fixed [ID] : MAX([AfterScore]) }= { fixed [ID] : MAX([BeforeScore]) } then 'No Change' else 'NULL' END

I'll try to post a workbook that could help.

-Chris

Edit: Attached a workbook, although not sure if it will help. "After Score" and "Before Score" are the fields you created, "Status" is the new calculated field.

1 of 1 people found this helpful
• ###### 3. Re: Calculation field Problem??

As per my understanding your count should be like this. Others coming from where you've ID='No' and as per your logic you're only considering ID with After or Before. But I've noticed you've count the IDs with 'No' also. So, I've put it in others. Hope you understand.

and % of Total Should be

Go through the workbook (version 9.3) attached for your reference. Feel free  to ask If you've any query.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: Calculation field Problem??

Hi Chris,

Thank you very much for your response.It worked for me perfectly.I am happy.

But i am getting Null as well as shown below. How to remove this NULL.

Can you please help me with this?

Best Wishes,

Sandeep

• ###### 5. Re: Calculation field Problem??

Hi Mahfooj,

Thanks for the reply. I just replied to Chris. Could you please answer my question if you know?

Best Wishes,

Sandeep

• ###### 6. Re: Calculation field Problem??

I guess its coming for those IDs which are 'No'

1 of 1 people found this helpful
• ###### 7. Re: Calculation field Problem??

Got it. I just used Status in the filter and removed NULL. Very helpful answers.

Thanks once again.

Best Wishes,

Sandeep