cancel
Showing results for 
Search instead for 
Did you mean: 

Sales order count split based on Sales Group in BEx

ms_k
Explorer
0 Kudos

Hello All,

I am looking for a BEx report which displays the No of sales order from billing cube and split the count into two columns(Orders <10000 & Orders>10000) and also the Sales order value with the same split. Can we achieve this logic in BEx query designer? Please provide your valuable suggestions.

No of Orders% of Total OrdersOrder Value $ Orders < 10k Orders 10< 20k Orders < 10k Orders < 20k Orders < 10k Orders < 20k

Regards,

MSK

Accepted Solutions (1)

Accepted Solutions (1)

former_member586947
Contributor

Hi,

Create the query as below and try.

1. Keep the sales group in the rows.

2. Create a formula (KF1) by taking billing value keyfigure and apply exception aggregation as "Count of all detailed values" and keep the reference char as sale order.

3. Now you can create two more formulas to display whether the count is more than 10000 or less.

A). Formula-1:

Orders less than 10000

FM 1= ( (KF1) < 10000 ) * 0

B). Formula-2:

Orders more than 10000

FM2 = ( (KF1) > 10000 ) * 0.

The above formula displays 1 if the condition is true or else value will be zero. You can display the sale order values instead of zeros and ones if you want.

If you can post the sample data and expected result set then we can think better ways to implement the solution.

Regards,

Satya.

ms_k
Explorer
0 Kudos

Thanks Satya. It helped to resolve my query.

Regards,
MSK

former_member586947
Contributor
0 Kudos

Hi,

Hope you have identified the mistake in the above formulas, instead of multiplying with zero you should multiply with KF1 in both the formulas FM1 & FM2.

Regards,

Satya.

Answers (1)

Answers (1)

anshu_lilhori
Active Contributor

Hello ,

You can achieve this using if else condition at BEx level.

(order value < 10000) * 1 + (order value > 10000) * 0

in the same way you can write for other column.

Make use of boolean operators for writing the same.

Hope this helps.

Regards,
AL