7 Replies Latest reply on Aug 26, 2016 1:48 AM by lei.chen.0

# Calculaltion Field going wrong

Hi All,

I have two numeric fields and a date field.

1.) Field 1

2.) Field 2

3.) Date

I have used below code to create a new calculation field based on the latest date as shown below:

if [Date]= {FIXED : MAX([Date])} then

SUM([Field 1])/(AVG([Field 2]))

END

But it is throwing the below error:

"Cannot mix aggregate and non aggregate comparisons or results in 'if' expressions"

Is there any way to get the value of that division based on the latest date?

Could someone help me on this?

Thank you.

Best Wishes,

Sandeep

• ###### 1. Re: Calculaltion Field going wrong

Hello Sandeep,

if [Date]= {FIXED : MAX([Date])} then

SUM([Field 1])/(AVG([Field 2]))

END

I tested the calculated field and it seems that

[Date]= {FIXED : MAX([Date])} is treated as not aggregated, while SUM([Field 1])/(AVG([Field 2])) is aggregate.

If I change SUM([Field 1])/(AVG([Field 2])) into something not aggregate, there is no error.

I attached a screen shot to demonstrate how to get the SUM([Profit])/AVG([Sales]) for the max [Order Date],

based on superstore data. You can filter [Calculation1] and get only one line for 2014/12/31 .

Regards

1 of 1 people found this helpful
• ###### 2. Re: Calculaltion Field going wrong

Hi Sandeep,

You can create two value before  SUM([Field 1])/(AVG([Field 2]))

----

-----

-Dieu

1 of 1 people found this helpful
• ###### 3. Re: Calculaltion Field going wrong

Thanks Dieu:)

• ###### 4. Re: Calculaltion Field going wrong

Hi Lei Chen,

Thank you for the reply.

Will there be any change if I use fixed function in the code you mentioned?.

If [Date]= {FIXED : MAX([Date])} then 1 else 0 end

Thank you.

Best Wishes,

Sandeep

• ###### 5. Re: Calculaltion Field going wrong

Hello Sandeep,

{FIXED : MAX([Date])} and {MAX([Date])}

They are the same

Regards

2 of 2 people found this helpful
• ###### 6. Re: Calculaltion Field going wrong

Thanks Lei Chen for all your help

• ###### 7. Re: Calculaltion Field going wrong

Happy to have been helpful