Excel formula - how to create similar formula in Business Objects

[KrisAlesh]

I am trying to create a formula in Business Objects similar to this Excel macro.

=Sumif(B:B,C2,E:E)+D2. The formula is in column E.

B Contains Material Nr at the supply location, C Material Nr at the delivery location, D Forecast at the delivery location. Thus this formula gives the total forecast (including downstream forecast) on each Material nr at delivery location.

I am failing to work out how to do this in Business Objects. Can someone help?

thanks

 

[hilfy]

So, if I understand this correctly, you want to compare each value in column B with the constant in C2 and sum the corresponding value from column E if B = C2. Is that correct? If so, I think this will work....

Since your column E is a formula, you first need to create that formula in your Webi, using the appropriate objects from your query. I'll call this "Formula_E" in this example.

Then you'll create another formula that looks something like this (I'll call this "Calc"):

=If([Object_B] = [Object_C2];[Formula_E];0)

And then the last formula looks like this:

=Sum([Calc])

You'll use this last formula on your report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[KrisAlesh]

Hi Dell, thanks for responding to my request. I'm afraid my explanation wasn't all that clear - either that or I don't understand how to write the formula you suggest.

Here I have made up some sample data to illustrate it better:

B C D E
Mat Nr Mat Nr Forecast =sumif(B:B,C2,E:E)+D2
at From Loc at To Loc giving this result:
12345 A 12345 B 100 700
12345 B 12345 C 100 300
12345 B 12345 D 100 100
12345 C 12345 E 100 100
12345 C 12345 F 100 100
12345 B 12345 G 100 100
12345 B 12345 H 100 100

At no point will the value in column C equal the value in column B. I don't know how to write the formula so that it "looks up" the value from B to attribute it to the value in C. I have written a formula to calculate the total forecast per Mat Nr at From Loc (=([Fnl Fcst SCU]/3)In([Query 2].[SOS ID];[From Facility Code]))but don't know how to attribute that to the relevant Mat Nr at To Location.

Thanks for your help with this!

 

[hilfy]

Is the value in C2 in your spreadsheet a constant? If your C column doesn't contain the data for the comparison, where is that data located?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com