COUNT and MAX logics in BEX query Designer

[venkat_korivi]

Hi Experts,

I am into implementation project, I have below scenarios which will have to derive from BW and BEX.

1. Client needs No.of data missing count in Report.

Ex:

                 01/02             02/02          03/02      Data missing count

Plant          2000              -                30000           1

                   -                    -                40000            2

                  10000             5000        60000            0

Based on the plant ,calday and keyfigure he want to check no.of count for data missing.

2. Max orders filled based on the Customers.

2.Min orders filled based on th customers.

Kindly please help how to implement these logics  in Bex designer .

Thanks in Advacen,

Chinna

[Loed]

Hi,

Can you try this?

Let's assume your keyfigure is called KF_VALUE..Create a formula, let's call it KF_FORMULA, and enter this equation..

KF_FORMULA = ( KF_VALUE == 0 )

If it did not work, apply EXCEPTION AGGREGATION to your kf_formula, choose SUM, then choose CALDAY as the reference characteristic..

Regards,

Loed

[venkat_korivi]

Hi Loed,

I have tried all the data functions in many possible ways, but I did not get expected result.

The main problem is while loading data in cube level 0 values are showing with BLANK SPACE.. hence, formula is not considered blank SPACE..

Thanks,

Chinna

[Loed]

Hi,

So why is it blank? Check your transformation..

Regards,

Loed

[venkat_korivi]

Source data is like that.

Requirement is like ... how many members are sold items per day with amount.

Ex:

               cal day 

                01/02           02/02

Plant       No.of shifts  

001         1   and 2   count          1 and 2   count

               -         2500    1       2000       2500  0.

From above example, for each shift how much money he has generated ...In 1(Morning) shift on 01/02 he did not generated any money so count 1. on 02/02 he was generated onet in both shifts so count is 0.

Blank will maintain in SAP ERP system. same thing loading in Cube level also. ofcourse we can write source level routine to replace with 0. but we are trying if there is any logic in Bex side.

Please help me if there is any logic for the same
.

Thanks

Venkat

[Loed]

Hi,

If it's a keyfigure, BLANK SPACE is also EQUAL TO ZERO..What did you try so far?

Tried the solution I have suggested in our system and it worked..I thought you already tried it?

Since I don't have enough data, my formula in KF_FORMULA is <= 10..I applied EXCEPTION AGGREGATION to it, chose SUM, and used POSTING DATE as the reference characteristic..

If you only want the RESULT part of KF_FORMULA and don't want to display the value for each day, just HIDE the CALCULATE SINGLE VALUE AS in your query designer..

This will be the result..

Regards,

Loed

[venkat_korivi]

Hi Loed,

Many Thanks for solution..

I reached almost near,  I have applied formula KF==0, then exception aggr as SUM reference char as shift code.

But In my case I want count for whose are not sold for particular shift.

Ex: 

       01/03                         01/04

      Shift code    count                 shift code

      A        B                              A           B         count

      0        0           2               2500        3500     0

In my output over all result is showing only 0.

[Loed]

Hi,

Your reference characteristic should be CALDAY or the date you are using in your date..Change it to CALDAY and you will achieve your desired result..

Regards,

Loed

[venkat_korivi]

Hi Loed,

Exception Aggregation as SUM and Ref Char as 0CAL DAY...

Formula: KF==0.

I have mentioned above all in Bex but still I didn't get desired result.

Kindly please help me. 

[venkat_korivi]

Hi Loed,

Where ever value exist for key figure then BEx formula givens the exact count but in my situation wherever value exist the it shouldn't be count. Wherever value doesn't exist then it should be count the number. 

Could you try this scenario in your system.

[Loed]

Hi,

I tried it in our system..

As you can see in the pictures I have uploaded, it counted the figures with ZERO value..I only used <= 10 in my image below since I lack data in our DEV system..But as you can see it is also counting values with ZERO..

Regards,

Loed

[Loed]

Hi,

Is 0CALDAY the date that you are using in your query? Can you post your query designer layout? Please also post the FORMULA you have used and SAMPLE REPORT OUTPUT using your current query..

Else, just use KF < 1 as the formula, assuming that you will not have NEGATIVE numbers and DECIMAL values between 0 to 1..

Regards,

Loed

[venkat_korivi]

Hi...

when your KF having 0 Values then its against count has been displayed because Physically 0 has displayed in your key figure.. coming to my situation 0 Physically is not displaying (In source also same as per requirement) ..Instead of 0. SPACE has maintained.. so I need count of space based on Calday or any other characteristic....

[Loed]

Hi,

As I have said, IF IT IS A KEYFIGURE, ZERO or SPACE are the same..It's only in your setting of query..Go to DISPLAY property, I think since I'm at home right now, and you have a choice how you wanted your ZERO values to appear, like ZERO, ZERO WITH CURRENCY, SPACE, etc..

Just do what I have suggested..

Make your formula KF < 1 or KF == 0..I did it in our system so it should also work in yours..

Regards,

Loed

[sapna_jgurudutt]

Hi Chinna,

Can you please create a Condition for the relevant Query in Query Designer with Operator Top N and Bottom N and check whether it helps you.

Thanks & Regards,

Sapna

Message was edited by: Sapna J Kini

[venkat_korivi]

Hi Sapna

Condition works work on whole query.

But my requirement is different... I want number of count against keyfigure which is showing as SPACE..