Why do I get a Multivalue# error in report that tries to sum 0 or empty values?

[dirk_peterson]

When I run a report, I get the multivalue error. More below.

I added a an account field to see what is trying to be added together to identify the repeating values. It appears that the repeating values are - or 0. See screenshot. More below.

I tried to update the variable that populates this column to be the variable + 0 so that I would always get at least 0 but this didn't help. Does anyone know what is causing this since the value on each row is the same and how to resolve this?

[dirk_peterson]

This same error occurs when - and a number are aggregated. The following 2 fields also produce a multivalue error when the account - name field is removed.

[dirk_peterson]

I checked the field format and changed it to 0 instead of '-' when 0 but this didn't help either.

[nscheaffer]

Where is the dash coming from? It cannot be in the data. The datatype of your Pre-June Adjustments - Approved DC object must be numeric otherwise you would not have to option to apply a format.

[dirk_peterson]

Noel, There are 2 theories. One is that the undefined values are responsible for the errors. The other is that the data types for BAL_SHT and RCASH are not the same and cant be aggregated.

Everything with an RCASH only or an RCASH and BAL_SHT account produces the error. Here is the table before I remove the account field that is allowing me to see the detail values before aggregation.

And here is the table after I remove the account field. More below..

Here is a sample variable where RCASH is used.

=If [Account Type] = "E" Then [Pre-June Adjustments - Approved] Where ([Form Category] InList ("3700_11"))

ElseIf [Account Type] = "R" Then -[Pre-June Adjustments - Approved] Where ([Form Category] InList ("3700_11"))

ElseIf [Account Type]="B" And [Account] = "RCASH" Then

(

If IsNumber([Pre-June Adjustments - Approved] Where ([Form Category] InList ("3700_11"))) Then

-[Pre-June Adjustments - Approved] Where ([Form Category] InList ("3700_11"))

Else

0

)

Else [Pre-June Adjustments - Approved] Where ([Form Category] InList ("3700_11"))

[dirk_peterson]

I thought I fixed this but didnt. It turns out my variable, the one I used in the table, was not accurate and was allowing different branches of logic to return the same data. This is what caused the multivalue error. I concluded that it is best to filter universe measures using a where clause. This is more efficient, and if the if statement variable is not in your table, I think an undefined error can result. This is an example of my redefined variable where I moved account type from the if statement to a where variable.

=[Pre-June Adjustments - Approved] Where (Not ([Form Category] InList ("3700_11")) And [Account Type]="E")

+

-[Pre-June Adjustments - Approved] Where (Not ([Form Category] InList ("3700_11")) And [Account Type]="R")

+

-[Pre-June Adjustments - Approved] Where (Not ([Form Category] InList ("3700_11")) And [Account]="RCASH" And [Account Type]="B")

+

[Pre-June Adjustments - Approved] Where (Not ([Form Category] InList ("3700_11")) And [Account] ="BAL_SHT" And [Account Type]="B")