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

Calculaltion Field going wrong

Sandeep Kotha Aug 25, 2016 8:47 PM

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

lei.chen.0 Aug 25, 2016 9:25 PM (in response to Sandeep Kotha)

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
Like Show 1 Likes(1)

Actions
2. Re: Calculaltion Field going wrong

Dieu Nguyen Aug 25, 2016 11:23 PM (in response to Sandeep Kotha)

Hi Sandeep,
You can create two value before SUM([Field 1])/(AVG([Field 2]))
----
-----
-Dieu
1 of 1 people found this helpful
Like Show 2 Likes(2)

Actions
3. Re: Calculaltion Field going wrong

Sandeep Kotha Aug 26, 2016 1:14 AM (in response to Dieu Nguyen)

Thanks Dieu:)
Like Show 0 Likes(0)

Actions
4. Re: Calculaltion Field going wrong

Sandeep Kotha Aug 26, 2016 1:17 AM (in response to lei.chen.0)

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
Like Show 0 Likes(0)

Actions
5. Re: Calculaltion Field going wrong

lei.chen.0 Aug 26, 2016 1:26 AM (in response to Sandeep Kotha)

Hello Sandeep,

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

They are the same

Regards
2 of 2 people found this helpful
Like Show 1 Likes(1)

Actions
6. Re: Calculaltion Field going wrong

Sandeep Kotha Aug 26, 2016 1:34 AM (in response to lei.chen.0)

Thanks Lei Chen for all your help
Like Show 0 Likes(0)

Actions
7. Re: Calculaltion Field going wrong

lei.chen.0 Aug 26, 2016 1:48 AM (in response to Sandeep Kotha)

Happy to have been helpful
Like Show 0 Likes(0)

Actions