Skip to Content
0

How to calculate the sum but ignore some variable in Webi

Jul 17, 2017 at 07:40 PM

65

avatar image
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?

Thanks in advance

Best Regards

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

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
10 |10000 characters needed characters left characters exceeded
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?

0

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

0

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

https://blogs.sap.com/2015/09/17/how-to-handle-aggregate-functions-like-countmaxmin-in-webi-summary-reports/

0
Former Member
seshu P

Hi Seshu

That's a great answer! Thank you for your help!

0