4 Replies Latest reply on Apr 18, 2013 9:16 PM by Joshua Milligan

# Calculation Rollup to Error instead of 0

I have data like this:

year
# of things
2011NULL
2012NULL
2013100

I have a parameter that will filter by year or display a sum of all time.

When I sum all time, the sum of "# of things" rolls up to 100. It sums 2011 and 2012 as 0. Instead, I'd like to have it display an error.

I've tried several different approaches of a calculated field but none have worked. The result needs to be a continuous measure or an error. This feels like a fairly straightforward function but I can't find it. Any help is appreciated.

Thanks,

Bryce

Some attempts at calculated fields:

IF ZN([# of things]) == 0

THEN "ERROR"

ELSE [# of things]

END

# fails because return values are not same type

IF ZN([# of things]) == 0

THEN "ERROR"

ELSE STR([# of things])

END

# fails because not continuous measure

• ###### 1. Re: Calculation Rollup to Error instead of 0

if sum([# of things])=0 then "Error" else str(sum(([# of things]) end

I would guess you have an aggregation on  # of things.With sum, I don't think you need NULL, but if you do:

if isnull(sum([# of things])) then "Error" else str(sum(([# of things]) end

• ###### 2. Re: Calculation Rollup to Error instead of 0

I do have an aggregation on # of things, so when it's typed as a string, it can't sum properly.

There's really no setting to rollup as an error if a value in the set is null?

• ###### 3. Re: Calculation Rollup to Error instead of 0

Did you try either of my calculations? One should work - you are not summing a string, you are making the summed value a string

• ###### 4. Re: Calculation Rollup to Error instead of 0

Bryce,

You may need something like the following if you are looking for a calculation that rolls up to different levels of aggregation, but displays an error if any row-level values are NULL:

IF

MIN(  IF ISNULL([# of things]) THEN 0 ELSE 1 END ) == 0

THEN "Error" ELSE STR( SUM([# of things]) ) END

What this does is check to see if a NULL occurs for any values at a row level.  If any of the values are NULL then the MIN will be 0 and the entire calc will return "Error".  Otherwise, you'll get the sum of the field as a string.

Regards,

Joshua