1 Reply Latest reply on Feb 27, 2018 2:13 AM by John Powell

Morning all!

I'm building a report to see if our users are hitting their weekly targets.  There is a points system in place where for some things they get 1 point for others they receive up to 3 points, this is in place so it's harder to trick the system by doing the easier tasks that don't really drive the business forward.

I've attached the workbook, this part of the report is currently unnamed so is just "Sheet 16".

For the points I've used several IF statements (under measures folder "Points Calcs") that for example look like this:

IF SUM([TotInts]) = 4 THEN 1

ELSEIF SUM([TotInts]) = 5 THEN 2

ELSEIF SUM([TotInts]) >= 6 THEN 3

ELSE 0

END

Tested each of these against user activity and each are bringing back the correct number of points.

Then for the points I've just got each of those added together (TotalPTS):

(ZN([CandPTS]+[ContPTS]+[CVsPTS]+[JobsPTS]+[IntsPTS]))

This is where it gets odd in that that does work!  At least for some of the rows.  Each row where the value is greater than 0 the number of points for that user and for that week is correct.  Every few rows however I get a result of NULL but using the ZN this get's translated to 0, it's these ones where it falls down as they do have points and I can't work out why it works for maybe half the rows but not the others?

I tried using:

{INCLUDE [_Date],[Consultant]:

(zn([CandPTS]+[ContPTS]+[CVsPTS]+[IntsPTS]+[JobsPTS]))}

But this just produced incorrect numbers.

• ###### 1. Re: Calculated field working sporadically

OK ignore me and feel free to send me abuse because I spotted what it was and it didn't work because I'm an idiot! :-)

Missed an "Else 0" on one of the calculations to get the points for an individual measure.

All working now.