Former Member

# Calculated variable throwing #MULTIVALUE error

I have data of employees entering time by day and time can be billable and non-billable.

I want to show a column chart where I show the number of employees categorized as per following buckets based on their NB%

"75% and Above", "50-75%" and "0-50%"

To achieve this I created a variable (measure) first that calculates the NB% and then created another variable called NB Group which basically is a set of If, ElseIf conditions that check NB% and assign value as "75% and Above", "50-75%" and "0-50%".

When I create a report with columns employee, NB% and NB Group, the NB Group value shows up fine. However when I just pull NB Group into a blank report I am getting #MULTIVALUE error.

I was expecting to see a distinct list of all the NB Groups just like it would for any other dimension and then wanted to add a new column with count of employees and convert that into a chart.

What am i doing wrong? Is there another approach I should follow?

10|10000 characters needed characters exceeded

### Related questions

• Posted on Jun 07, 2014 at 08:16 AM

Hi,

Not sure, but you can give it a try.

Check the option "Avoid duplicate rows aggregation".

hope it helps.

Thanks,

Rishabh

10|10000 characters needed characters exceeded
• Former Member

Tried that already. It shows every single record which isn't what I want. I want it to show distinct set of NB Groups and count for each group.

• Former Member
Posted on Jun 09, 2014 at 06:11 AM

It is not able split into multiple records probably due to a context.

Can you provide the sample code for NB Group?

10|10000 characters needed characters exceeded
• Former Member
Posted on Jun 09, 2014 at 12:21 PM

Hi Vivek,

Can you please the share the IF THEN ELSE logic used?

This error comes when there are multiple values returned by a formula and Webi tries to accommodate them in a single cell.

Reading your description, I am assuming a scenario which could be correct:--

Suppose the values returned by NB% are 75%,80% and 85%. As per your group variable, all these values should fall under "75% and Above" group; however, it is String and not a numeric and this value cannot be aggregated.

So, Webi will get "75% and Above" value thrice for 75%,80% and 85% values which it will try to accomodate in single cell resulting #MULTIVALUE error.

Try using min() or max() on the group variable and check if it helps. As per my suspicion repeating values are getting returned by the IF THEN logic; so, either of the functions can work for you.

Hope this will help.

Regards,

Yuvraj

10|10000 characters needed characters exceeded
• Former Member Former Member

Both [Hours] and [Non-Billable Hrs] are report level variables.

[Hours] = [Hours Entered] + [Hours Missing] (Both of these are coming from the DB/Universe)

[Non-Billable Hrs] =Sum([Hours]) In ([Employee]) Where ([Hrs Type] = "Non-Billable")

[NB Group] =If [Non-Billable Hrs]/ Sum([Hours]) > 0.5 Then "50% and Above" Else "0-50%"

Note: [NB Group] is getting converted to a measure automatically and i am not able to change it. Wondering if this is causing the problem?

Anyway, as you suggested I tried to take a small data set and get the results per Employee and that came out correctly (top table).

However, when I remove the employee and try to aggregate I am not getting the expected count.

Here, Employee =Count([Employee])

NB Group.jpg (39.8 kB)