IF Else at Bex Query

[Former Member]

Hi All,

My requirement is to Calculate Formula for Net Sales in Bex Query.

If Incoterms = 'DDP'

Net sales = Gross Sales-(Freight DDP-Freight Discounts)

Else

Net Sales = Gross Sales

For this what i did is,

1)Created Calculated CKF_1 and Passed Value '0'(Zero)( dummy ckf)

2)Created RKF_1 and restricted above CKF_1 with Incoterms = DDP

3)CKF

In formula pane written as like below

RKF_1 * (Gross Sales - (Freight DDP- Freight Discounts)) + Gross Sales

But i am not able to fetch the value when freight = DDP

Please let me know how can i acheive the Value.

Pls do the needful.

Thanks.

Regards,

Seshu

[yasemin_kilinc]

Hi Seshu,

Please try the following procedure.

1. add Gross Sales as KYF1

2. Create CKF1: formula as: Freight DDP - Freight Discounts. Hide this CKF1.

3. Create RKF1: Add CKF1 as key figure and restrict with Incoterms = DDP.

4. Create another formula as KYF - RKF1.

Hope it helps

Regards

Yasemin...

[Former Member]

Hi Yasemin,

Thanks a lot for your answer.

Its working fine.

Net sales field is having in around 10 reports in my production.

Now, I created Net sales as a global variable in development system.

How can i move the changes to reflect my newly created net sales in production in all the reports.

Do i need to make changes for net sales in all reports in my dev and move them to production or i can change in one report and transport.

If i make changes in one report and move do all reports net sales will change.

Thanks.

Regards,

Seshu

[yasemin_kilinc]

Hi Seshu,

I am not sure if I get your question. If you have created the net sales (the final formula) as a Calculated Key Figure on the left pane of the query designer, then you can use this new created CKF in all of your queries from the same infoprovider. In each of them you need to add this new CKF instead.

But if you have created the final formula as a formula you will only be able to use it in this query. So make sure that you have created a global CKF. when you open other queries you will see this new CKF under Calculated Key Figures on the left pane. Then you can make the change to all other reports using this CKF, then transport all queries.

Regards

Yasemin..

[sai_adapa]

Hi Yasemin,

we have similar issue.

In my BEx query I need to show below records.

1. Records with material type 'FERT', here I need to show only records where qty GT 0.

2. Records with material type NON FERT ( Other than FERT) , I need to show all records.

For this.

a. I have created one dummy CKF with value 1.

b. Created RKF1 where restricted dummy CKF with material type = FERT.

c. Created CKF2 with formulae as below.

( RKF1 == 1 AND Qty <> 0 ) * Qty + ( RKF1 == 1 AND Qty == 0 ) * 1 + Qty.

d. This CKF2 will put 1 for FERT records where qty 0 and by using condition on CKF2  I will filter these 1's.

But the issue is

RKF1 is showing 1 for all the records ( FERT and Non FERT ), if there are more key fields in the output.

If I put only RKF1 in the output it is showing correctly.

Am I doing some thing wrong. Please advise.

Thanks...

Sai

[yasemin_kilinc]

Hi Sai,

I am sorry that I couldn't get your question. What do you mean by showing all records with NON FERT?

I will try to help if you can share the desired output with a sample. By the way

( RKF1 == 1 AND Qty <> 0 ) * Qty + ( RKF1 == 1 AND Qty == 0 ) * 1 + Qty.

This formula will work exactly the same way you mention. RKF1 is simply the count of records with material type FERT. So as you add the key fields it will show 1 when all fields are included.

I think it would be better if you create a new post for this problem.

[sai_adapa]

Hi Yasemin,

I have already created new post.

Can you please check the below link.

[Loed]

Hi Sesha,

Are the freight DDP, freight discounts, and gross sales all KEYFIGURES?

The problem with this is we cannot use NON-NUMERIC value in a formula..So we cannot detect if INCOTERMS = DDP..

May I know the purpose of these two (2) steps?

1)Created Calculated CKF_1 and Passed Value '0'(Zero)( dummy ckf)

2)Created RKF_1 and restricted above CKF_1 with Incoterms = DDP

Regards,

Loed

[Former Member]

Hi Loed,

For Your Information these freight DDP, freight discounts, and gross sales are all KEYFIGURES.

For ur Question:

Step-1: Create a dummy CKF (DDP_CKF1) and in formula pane keep '1' .

Step-2: Create a RKF (DDP_RKF1) on above created CKF with restriction of WA_SALESREP-INCOTERMS1 = DDP . 

Step-3: Now keep this RKF in keyfigure pane and hide it.

Step-4: Now create formula like below

(DDP_RKF1 = 1 * (WA_SALESREP-GROSSSALES - ( WA_SALESREP-FREIGHTDDP -WA_SALESREP-FREIGHTDISCOUNT )) + WA_SALESREP-GROSSSALES

Thanks.

Regards,

Seshu