0

# How to calculate the sum but ignore some variable in Webi

Jul 17, 2017 at 07:40 PM

65

Former Member

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?

Best Regards

AMIT KUMAR
Jul 17, 2017 at 08:28 PM
0

use this formula.

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

untitled.png (17.7 kB)
Show 4 Share
Former Member

Hi Amit

Thank you for your answer. But the table I wrote in the question is just an example. The real data has more than 300 stores with 10 different Group levels. Do you have any advice for data sets like that?

Former Member

if you have 10 grpup level then even you can try with multiple if-else condition.

Convert that measure object to dimension , and apply sum of this dim .

Former Member
seshu P

Hi Seshu