3 Replies Latest reply on Apr 19, 2018 4:11 PM by Cynthia Derksen

# IF THEN AND BETWEEN Calculation

I have a calculation that I am using to color my table.  It has four colors Red for > 5% below goal (Red); Yellow is within 5% of goal; and

Light and Dark Green for those who have met goal.    Only the green colors of my formulas work. I reversed the order of the formula hoping that

make it work.  I am sure there is a simple answer that I can't figure out.  Please share your knowledge.

//Greater than 5% below goal

If ([Rate]<([Goal]*0.95)) THEN "Red"

//Less than Goal but within 5% of Goal

ELSEIF (([Rate]<[Goal]) AND ([Rate]>([Goal]*0.95))) THEN "Yellow"

// less than 100% and equal to or greater than Goal

ELSEIF ([Rate]>=[Goal])THEN "Light Green"

// outstanding score is 100%

ELSEIF ([Rate]=1) THEN "Dark Green"

END

I am trying to do conditional formatting on my table.

• ###### 1. Re: IF THEN AND BETWEEN Calculation

Cynthia,

Are [Rate] and [Goal] aggregates? Without seeing the actual data my first thought would be to wrap them in an appropriate aggregate (probably SUM or AVG) depending on your data.

Did that help?

-Wesley

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: IF THEN AND BETWEEN Calculation

Is [Rate] and [Goal] the same datatype and the same order of magnitude ?

Usually in this sort of situation I create a new sheet showing [Rate], [Goal] and [the calculated field] to see where the problem is in the logic.

Could it be a aggregated/non-aggregated issue ?

Some sample data would be great if you need more help

• ###### 3. Re: IF THEN AND BETWEEN Calculation

This is the current months data so, it selected by Max Date.

The rate is simple num/denom =Rate for numerous measure.

There are 6 locations and one of them is the GOAL.

The goal is a national benchmark which changes between each measure.

So, I created calculation of Goal is culled by

IF ATTR([Location]="GOAL") THEN ([Rate]) END

My table design is all locations are columns and the measures are rows with the T label being AGG(Rate)