Nested if/then with variables returning #MultiValue ERROR

[former_member957245]

Hi all,

My Company recently switched from Power BI to SAP BO (using Webi to Dev/Test) and I am currently validating the functionality with our currently use case. The Goal of this report is to identify item set up issues and report them to the user on a “Dashboard” and to the user. I am struggling with a #MultiValue Error being returned when using Count

For Example:

Variable1

=If(Column1 = 0 and Sum(Column2 + Column3 + Column4)= 0 ; “Zero”;

If(Sum(Column2 + Column3 + Column4) > 5 ; “A”;”B”))

Variable2

=If(Column5 = “Y”

OR Variable1 inlist (“A”;”B”;”C”)

OR Column6 inlist (“D”;”E”;”F”) ; “OK” ;”KO”)

Variable3

=Count(Items) Where Left (Variable2;2) InList (“KO”)

When I count the items with a KO and aggregate by another column (Buyer Name) I receive the #MultiValue Error. Looking at the Buyer/Item level no issue counting a 1 when there is a KO, but only when aggregating by another column (location, buyer name, ect…)

I guess my main questions are…

What could be causing this error for my use case?

Are there any specific pit falls when using Count in SAP BO?

Am I missing a glaring obvious assumption I am making that could cause this error?

When I use the count function in the exact same way in other systems, (Power BI, Excel, SQL, ect… ) it doesn’t have this issue.

Thanks for your help and patience as I am learning and googling my way though this.

[moshenaveh]

Welcome to the SAP Community! Thank you for visiting us to get answers to your questions.

Since you're asking a question here for the first time, I'd like to offer some friendly advice on how to get the most out of your community membership and experience.

First, please see https://community.sap.com/resources/questions-and-answers, as this resource page provides tips for preparing questions that draw responses from our members. Secondly, feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html as well, as that will help you when submitting questions to the community.

Finally, I recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

[ayman_salem]

Can you provide us with sample data and a screenshot of the result you want so we can give you the possible correct answer.

[drs_macgyver]

I can't offer a solution from what you have provided but I suggest that you go into Format Table and turn on the check box for Avoid Duplicate Row Aggregation. This will show you all the rows, not aggregated, and you might be able to determine why you are getting multiple values for each aggregated dimension.