4 Replies Latest reply on Sep 20, 2018 10:46 AM by John Salazar

# How to calculate proportion of values in a field that fall within a range

Hello,

I am trying to create a stacked area chart that shows percentages of values in a field that fall within a range.  For example, I am trying to calculate one segment of the area chart like this:

IF [Turbulence] >= -1 AND [Turbulence] <= -0.5

THEN 1/COUNT([Turbulence])

ELSE 0

END

I would then SUM() the resulting calculated value to get the count of Turbulence values that fall within the range.

However, I get the following error "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions".

How can I count up the number of turbulence values that fall within the range and then divide it by the total number of turbulence values?

Thanks,

John

• ###### 1. Re: How to calculate proportion of values in a field that fall within a range

Hi John,

Try this:

Field1:

IF [Turbulence] >= -1 AND [Turbulence] <= -0.5

THEN 1

ELSE 0

END

Field2

SUM(Field1)/SUM(Number of Records)

Regards,

Rahul

• ###### 2. Re: How to calculate proportion of values in a field that fall within a range

Hi Rahul,

I actually did that beforehand and it worked, but I'm wondering if it was possible to do it within a single field.  Otherwise the number of fields might get out of hand if I had to do more ranges.

If it's not possible though, I'll stick with your solution.

Thanks,

John

• ###### 3. Re: How to calculate proportion of values in a field that fall within a range

Hi John,

In just one field you can write this way :

SUM(IF [Turbulence] >= -1 AND [Turbulence] <= -0.5 THEN 1 ELSE 0 END)/SUM([Number of Records])

Let me know if it helps.

Regards,

Rahul

• ###### 4. Re: How to calculate proportion of values in a field that fall within a range

Worked perfectly. Thanks!