Hello-
I have a straight forward calculation set up and its working fine for one of the dates but not for another date. I'm not sure what is contributing to the error. Appreciate any feedback.
This is the calculation for 'Open Rate Overall Status
IF [Assessment %]<=-5 then "Very Good"
elseif [Assessment %]>-5<=0 then "Good"
elseif [Assessment %]>0<=5 then "Fair"
elseif [Assessment %]>5<=15 then "Poor"
elseif [Assessment %]>15 then "Very Poor"
else Null()
endif
Calculation for Open Rate RAG
if [Open Rate Overall Status]="Very Good" or "Good" then "Green" elseif [Open Rate Overall Status]="Fair" or "Poor" then "Amber" else "Red" endif
Results: highlighted in red is the date with incorrect Status and RAG. Correct results for 1/31/2021 should be "poor" and "Amber"
List of KPIs | Assessment | Reporting Date | As of Date | Assessment % | Open Rate Overall Status | Open Rate RAG |
Open Rate | 0.12 | 3/31/2021 | 1/31/2021 | 12 | Good | Red |
Open Rate | -0.063 | 3/31/2021 | 2/28/2021 | -6.3 | Very Good | Green |
Solved! Go to Solution.
Try this:
IF [Assessment %]<=-5 then "Very Good"
elseif [Assessment %]>-5 and [Assessment %] <=0 then "Good"
elseif [Assessment %]>0 and [Assessment %] <=5 then "Fair"
elseif [Assessment %]>5 and [Assessment %] <=15 then "Poor"
elseif [Assessment %]>15 then "Very Poor"
else Null()
endif
Thank you so much @Luke_C . Works perfectly now!!