Calculated variable throwing #MULTIVALUE error

[Former Member]

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?

[Former Member]

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

[Former Member]

Here's the code I have currently for NB Group

= If ([Non-Billable Hrs] / Sum([Hours]) ) >= 0.75 Then "75% and Above%" ElseIf ([Non-Billable Hrs] / Sum([Hours]) ) >= 0.5 Then "50-75%" ElseIf ([Non-Billable Hrs] / Sum([Hours]) ) >= 0.25 Then "25-50%" Else "0-25%"

Both [Non-Billable Hrs] and [Hours] are measures.

I tried putting Max around the above code and that gave an answer but everything fell into the "0-25%" bucket, I guess because it aggregated for all employees and then checked the result so now it looks like more of a context issue.

I've a few things using "In" but can't seem to get it right though. Any further suggestions on how to get what I am looking for?

[Former Member]

Do you have more than one employee in the buckets? In that case you may have to use group by in the Query editor window > SQL > Custom SQL.

[Former Member]

Yes I may have more than 1 employee in each bucket.

Isn't it possible to achieve this without Custom SQL?

[Former Member]

Then you have to group by and create a derived table in Universe. Custom SQL would be much easier and easy to maintain.

[Former Member]

Hmm.. You mean there is no way to achieve what I'm looking for using just report level formula/variables and contexts is it?

[Former Member]

One way, very tedious if you have large record count.

Use if statement, something like this -

if([EmployeeID],InList('50-75%),count(employeeid))

You have to create a variable for each percentage range like 50-75%,75-90%....etc. Haven't tried it but this could be a logic which needs some tweeking to get it done.

[Former Member]

Hi Vivek,

Are [Non-Billable Hrs] and [Hours] objects coming directly from database or created at report level?

If report level , what dimension objects have been used to create them? What are thier definitions? Are you taking those dimensions in the block or column table?

Do you get correct results if you drag them in your block?

If yes then pass those dimension as Input Context using IN operator in your formula and check.

First check the values in a vertical table and then convert it to a chart once the values are correct.

Regards,

Yuvraj

[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])

[Former Member]

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

Can you provide the sample code for NB Group?

[rishabh_singhania]

Hi,

Not sure, but you can give it a try.

Check the option "Avoid duplicate rows aggregation".

hope it helps.

Thanks,

Rishabh

[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.