SAP BI/BW - Calculate the total on individual Columns on BEX

[former_member810379]

Hello

I have a situation using BEX query.

The case is as follows, I have several customers for different type of products.

	Customer 1	Customer 2	Customer 3
PType1	10	15	20
PType2	15	1	11
PType3	3	3	10
Results	18	19	41

I need the total of products (18, 19, 41) on a calculated key figure for each customer to be used in another formula.

It should look like this:

	Customer1	KFTotal	Customer2	KFTotal	Customer3	KFTotal
PType1	10	18	15	19	20	41
PType2	15	18	1	19	11	41
PType3	3	18	3	19	10	41
Results	18		19		41

The issue I am having is that I am unable to get the total in the KFTotal key figure for each individual customer.

Until now, I have tried:

1. Use exception aggregate in a formula with option “Summation” in reference to customer.

      The issue here is that the KFTotal Keyfigure displays the individual value for each Product type instead of the total.

1. Use Data function SUMGT on the values for each customer. The issue here is that I am having the total for all customers instead of each individual customer. I.e, all KFTotal will display 78 (18+19+41). I cannot use exception aggregate while using SUMGT.

1. Use Data function SUMRT on the values for each customer. Same issue as with SUMGT.
2. Use Data function SUMCT on the values for each customer.  Values for KFTotal = X. I cannot work with this.

Can anybody please help me . How can I calculate the individual totals? I basically need the same total calculated locally for display in a calculated key figure.

Thanks

[Former Member · Accepted solution]

Dear John,

Using formula variables it will be tricky.

I tried with SUMCT and it is working fine.  Please see screenshots below.

1. Create Formula with data function SUMCT

2. Go to calculation tab of formula and hide "Calculate Results as"

3.For vendor make it Results row "Always Dsiplay"

4. See the below result

Thanks

[former_member810379 · Accepted solution]

Hi, this solution should work but the issue the structure of my report. Maybe I did not describe it correctly. Sorry. The two key figures (sales and sum of sales) are broken by the customer.  If I removed the customer, then the solution about would work.

The result should look like this :

	Customer 1		Customer 2		Customer 3
	Sales	Sum of Sales	Sales	Sum of Sales	Sales	Sum of Sales
PType1	10	18	15	19	20	41
PType2	15	18	1	19	11	41
PType3	3	18	3	19	10	41
Results	18		19		41

[Former Member · Accepted solution]

Hi John,

You mean after removing the customer it is working or After removing the customer it should work. Sorry i did not get it correctly?.

What do you mean by key figures are broken by customer. is that customer is not having the values?

Thanks

[former_member810379 · Accepted solution]

Customer is found above the structure of keyfigure in Columns. This means that I need the sum of sales for each customer and ptype. Having customer above the key figure structure breaks the sum and SUMCT is not returning any value because of the customer.

Thanks.

John.

[BernhardE · Accepted solution]

Hi John,

In case that you have not any filter set on characteristic " type of products" you may try using feature "Constant Selection" in order to realize your scenario:

Keyfigure Sum of Sales =  - Keyfigure Sales

                                           - " type of products" (flag Constant Selection)

Best regards,

Bernhard

[former_member810379 · Accepted solution]

Hi Bernhard,

Actually, I do have a filter on type of products.

[BernhardE · Accepted solution]

Hi John,

How is the filter on type of products defined in your query scenario? Is it possible to also put the same restriction into Keyfigure Sum of Sales? I think this should work.

e.g.:

Keyfigure Sum of Sales =  - Keyfigure Sales

                                           - " type of products" (flag Constant Selection)
                                              - PType1, PType2, PType3

BR Bernhard

[former_member810379 · Accepted solution]

Product type is on the selection screen, any number of product type can be chosen . I am not sure what you mean by having the same restriction on keyfigure sum of sales, is a calculated keyfigure that sums sales from a customer.

[BernhardE · Accepted solution]

Hi John,

I am a bit confused about your last answer.

Instead of using a calculated keyfigure for "Sum of Sales" I would suggest to try the following approach:

- For"Sum of Sales" create a keyfigure selection (or a restricted keyfigure) and:
   - Enter "Sales" as keyfigure
   - Add characteristic product type -> right click -> flag constant selection
   - Add the same restriction on product type as defined globally (e.g. restrict on the same variable which appears on the selection screen).

Kind of:

More information related to the Constant Selection feature can be found on our WIKI page:
OT-OLAP-CS - SAP NetWeaver Business Warehouse - SCN Wiki

[former_member810379 · Accepted solution]

This works great! Thanks! Thank you to all the others too, learned a lot of new concepts.

[Former Member]

Hi John,

I am predicting that you have some cells for key figures as "*". may be thats why you are getting "X".

Try to remove those and you will get the correct result.  Try with oss note 1013127 as well

Thanks

[Former Member]

Hi John,

Have got the solution for your requirement,if yes then please share with us so that it may helpful us .

Thanks,

sunil

[santosh_hiremath]

Hi John,

As kool Sudha explained, it should work.. as you said you are getting 'x'

Can you elaborate more so we can identify the gap(if possible with screen shots).

Regards,

Santu

[former_member810379]

Hi.

So I have implemented Sudha's solution.

1. Calculated key figure for the totals.

   2. Local calculations for that key figure:

   3. Settings for Row (Product type).

The Result is as follows;

	Customer1	KFTotal	Customer2	KFTotal	Customer3	KFTotal
PType1	10	X	15	X	20	X
PType2	15	X	1	X	11	X
PType3	3	X	3	X	10	X
Results	18		19		41

Note that if I diplay results for the KFTotal key figure, the correct total is display. So the issue is with the individual values which I need for another calculation. I have tried to display with NoDim but the issue is the same.

[former_member199945]

Hi,

Use customer Iobj in rows pane & set hide . Now try to run query u will get kf total also.

[former_member202718]

Hi John,

Without Exception..Cant we use it this way,

CKF1= Customer 1 values as you have shown...

CKF2= SUMGT/SUMCT (CKF1)

rgds

SVU

[ccc_ccc]

Hi John,

1 Create replacement path formula variable for customer 1 ,2 and 3 and keep all in COLUMNS

2 Place TYPE(PType1,2,3) characteristic in Rows and result always display

3 Create three formulas for customer 1 ,2 and 3 to display overall result like 18,19, 41 by using  SUMGT function to display side of each customer

Regards,

Nanda

[former_member810379]

Hello, thanks for your response.

I actually, the solution you are proposing requires that I know the number of customer. I did not mention that in the original post sorry, but the data used in the original post is an example. In practice, I will not know the number of customers, the solution should work for any number of customers.

[ccc_ccc]

Hi John,

Solution which I proposed work for all customer, I just explained you based on your example so that you can understand better.

Regards

Nanda

[Former Member]

did you try cells?