Skip to Content
author's profile photo Former Member
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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • 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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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

    Add a comment
    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)

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.