Using Extended references with an IF and Sum Aggregate?

[ordendelfai]

BO 5.1.7

I am just starting to use BO, and I have a report that I want to only display two dimensions and a custom variable that sums on <Total Eligible Count> only when the <Mbr Relationship Code> = "C" (the MBR Relationship Code is not in the table)

In the table:
1. <Calendar Year+Month>
2. <Total Eligible Count>
3. <MyCustomVariable>

Not in the table:
4. <Mbr Relationship Code>

The below custom variable works as long as the <Mbr Relationship Code> is in it, but when I take it out, I get a #Multivalue error. I have tweaked and played with as much as I can, and sometimes I can get a #Computation error. If you have any advice how to make it work, I would really appreciate it!

Custom Variable:
=If(<Mbr Relationship Code> In(<Mbr Relationship Code> , <Calendar Year+Month>) In <Mbr Relationship Code> = ("C")) Then Sum(<Total Eligible Count> In(<Mbr Relationship Code> , <Calendar Year+Month>) In <Mbr Relationship Code>)

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[ordendelfai]

I was able to find a workaround:

=Sum(<Total Eligible Count> Where(<Mbr Relationship Code>="C"))

Not sure why Where can reference dimensions outside the table, but hey, it works!

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[blom0344]

Joel,

In a number of cases the '#Multivalue' can be resolved by wrapping an aggregate around the variable.

Example:

Max(<variable>)

This may not be applicable to your case, but in other cases it does work. The WHERE clause in BO has restricted use, cause one is only allowed to reference fixed values. So much the better that it does the job in your case....

Ties Blom
Information analyst
tbl@shimano-eu.com