Skip to Content
avatar image
Former Member

How to calculate the sum but ignore some variable in Webi

Hi all,

I'm using BusinessObjects BI Platform 4.1 to work on some objects.

I have some data about the sales qty. The user want to create a variable called "Sales Group" base on the qty first and do some further calculation based on this group. The original data looks like:

Store, Color, Qty

1, Red, 200

1, Black, 100

2, Red, 50

2, Black, 100

3, Red, 100

3, Black, 100

[Sales Group] = If [Qty] ForEach ([Store])>200 then "A" else "B"

This code works well and I can get tables below.

Store, Group

1, A

2, B

3, B

But then I met some trouble. So I want to calculate the sum of each group and add the sum into the table like:

Store, Group, Group Sum

1, A, 300

2, B, 350

3, B, 350

I tried both

[Group Sum]=Sum([Qty]) ForEach ([Group])
[Group Sum]=Sum([Qty]) In ([Group])

But both give me a table:

Store, Group, Group Sum

1, A, 300

2, B, 150

3, B, 200

Do anyone has any idea about how to figure this out?

Thanks in advance

Best Regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 17, 2017 at 08:28 PM

    use this formula.

    =If([Group]="A") Then [Qty] Else [Qty] Where ([Group]="B") In Block

    Add comment
    10|10000 characters needed characters exceeded