4 Replies Latest reply on Mar 16, 2016 6:37 PM by Stephen Crocker

# 3 condition KPI calculation help needed

I need to figure out how to get a KPI of a 1, 2, or 0   so here is a sampling of results.  the query pulls in people with region and I'm using number or records to figure this out.

Region       Sum of Individuals            KPI

Central          1022                               ??   '

Calculated Field.

If rgn_code = 'Central' and [Number of Records] > 1112 then 2 elseif rgn_code = 'Central' and [Number of Records] < 999  then 0 else 1 end.

This is an example.  I have 9 total regions to work into this.  the calculation is not working my sum total is 1022 so the kpi should be a 1 because it's less than 1112 and greater than 999.

Any help would be greatly appreciated.  I'm trying to figure out 3 conditional calculations based on 2 fields.

• ###### 1. Re: 3 condition KPI calculation help needed

Difficult to investigate without actual data.

Are you able to share your packaged workbook (***.twbx) .

Thanks,

Shin

• ###### 2. Re: 3 condition KPI calculation help needed

The key to calculated fields is that they are always at the row level unless you explicitly use aggregates in your calculations.  Number of records will always be 1 at the row level.  You can create a fix LOD calc by region to get a # of records at the row level    {fixed [region] : sum(Number of Records] }

• ###### 3. Re: 3 condition KPI calculation help needed

It sounds like you are not aggregating it up. Try this:

If attr(rgn_code) = 'Central' and sum([Number of Records]) > 1112 then 2 elseif attr(rgn_code) = 'Central' and sum([Number of Records]) < 999  then 0 else 1 end.

• ###### 4. Re: 3 condition KPI calculation help needed

Thanks Derrick, I really appreciate the help.