Want some formula like sumifs, countifs to count unique or duplicate lines or rows in SAP BI Bex query designer.

[Former Member]

Hi,

All,

Kindly somebody help me to count unique and duplicate lines or row in SAP BI Bex query designer . I am new in this field. please help me...

using multiple criteria like used in Excel or SQL Query.

Regards,

Shiv

[Loed · Accepted solution]

Hi,

You may refer in my document..

Just post here for your questions..

Regards,

Loed

[Former Member · Accepted solution]

Sir,

I done done as per graph but where , i will have to go for  create infobject Conc_inf_obj....

Like will have  to go in intial stage when we create. Characteristics or Key figure or it can possible in Bex query designer. please Help.

Regards,

Shi

[Loed · Accepted solution]

Hi,

Yes, you need to create in the initial stage to concatenate all your objects..

I think this is also possible in query designer but you need to have NESTED/MULTIPLE EXCEPTION AGGREGATION (which will impact your query performance)..You need to use the COUNTER FOR ALL DETAILED VALUES and reference characteristic to all your objects you wanted to count as unique..

Regards,

Loed

[Former Member · Accepted solution]

Sir,

I want to make the report like attached file as developed in in excel. For developing the query feel some problem.

Kindly help me.

Sir as also want to  know about this field scope ? I have knowledge of Mysql,SQL,eXCEL,AND MS ACCESS.

Regards,

Shiv

[Former Member · Accepted solution]

Hello Shiv,

As suggested by Loed , Acception aggregation is the solution for the report you are asking.

Waiting for Loed's Response..with somehing new.

Thanks & Regards,

Maunank Patel

[Loed · Accepted solution]

Hi,

May I know the objects you wanted to check for UNIQUE combinations?

I did not try this yet in query but let's try..

For example, you wanted to know the UNIQUE count using these objects: OBJ_A and OBJ_B..

Create a formula, let's call it FORMULA_A, then insert 1 as its content..In EXCEPTION AGGREGATION choose COUNTER FOR ALL DETAILED VALUES then in your REFERENCE CHARACTERISTIC choose OBJ_A..

Create another formula, let's call it FORMULA_B, then insert FORMULA_A as its content..In EXCEPTION AGGREGATION choose COUNTER FOR ALL DETAILED VALUES then in your REFERENCE CHARACTERISTIC choose OBJ_B..

Using the OBJ_B, you will already get the TOTAL UNIQUE COUNT of the combinations of the two (2) objects..

Try it then post for the result..

Regards,

Loed

[Former Member · Accepted solution]

Sir can we set key figure in ref. characteristics... but not showing in ref....dropdown list.

[Loed · Accepted solution]

Hi,

You can't use key figure in ref.characteristic..The OBJ_A and OBJ_B are both characteristics..

Regards,

Loed

[Former Member · Accepted solution]

Thank you so much....but sir but also count duplicated  lines or rows in Key figures which values or qyt >0.. is it possible.

like

region       store     qty1     qty 2     qty 3

delhi        1111      1          0          0

pune        1112       5          3          2

bang         1113     20        10          6

here number of row >0 is 3 according to qty1 and according to qty2  and qty 3 is 2 .

How can we calcuate..pls help.

Regards,

Shiv

[Loed · Accepted solution]

Hi,

So you already have your QTY1, QTY2, and QTY3..

Create three (3) formulas, let's name them FORMULA_QTY1, FORMULA_QTY2, and FORMULA_QTY3..

For FORMULA_QTY1 enter this equation..

QTY1 > 0

In the aggregation tab choose SUMMATION and choose region and reference characteristic..

Do the same for FORMULA_QTY2 and FORMULA_QTY3..

Just post here for questions..

Regards,

Loed

[Former Member · Accepted solution]

Thank you so much sir.. got what i want .....

But Still one question is pending..

How to count duplicate lines as

region       store     qty1     qty 2     qty 3

delhi        1111      1          0          0

pune        1112       5          3          2

bang         1113     20        10          6

delhi        1111      1          0          0

pune        1112       5          3          2

bang         1113     20        10          6

delhi        1111      1          0          0

pune        1112       5          3          2

bang         1113     20        10          6

Here no of lines  are 9>0  according to Qty1 but no of lines are 6>0 according to qty2 and Qty3.

how can we solve it..?

Want to perferm any query on Qty1qty2qty3 only..

Regards,

Shiv

[Loed · Accepted solution]

Hi,

Sorry I did not get what you said..Can you rephrase your question?

Why do you have a copy of values for 3 times?

Regards,

Loed

[Former Member]

Hello expert.

I have below requirements, did as per expert guided but not get exact output.

Just want to show on  report layout no. of rows of every region.

region       store     qty1     qty 2     qty 3

delhi        1111      1          0          0

pune        1112       5          3          2

bang        1114     30        10          6

delhi        1115      14         10          10

pune        1116       5          3             0

bang         1112     20        10          6

delhi        1111     1          0          0

pune        1115     51         33          32

bang         1114     20        10          6

Here no of lines  are 9  in which qty1 contains qty>0  but no of lines are 7 according to qty2 which contains qty2  >0 as well in qty3 are 6 lines. you can see above.

OUtput should come like.

                              lines(rows)  in Qty1 qty2 and qty3

region          qty1>0               qty2 >0         qty3>0

Delhi               3                         1                    1             

pune               3                         3                    2

bang               3                         3                     3

so how can we get above result? If i check by store or by region output should same  as above.

but according to expert guidance got result like

region          qty1>0               qty2 >0         qty3>0

Delhi               1                        1                    1             

pune               1                         1                    1

bang               1                         1                     1

Waiting for your valuable input.

regards,

Shiv

[Former Member]

Hello 

Anybody please help me...... as above problems......

Regards,

Shiv

[Former Member]

Sir,

I want to keep Qty1,Qty2 and Qty3 in Ref..field when we set ref.characteristics. As per your guidance I have set three formulae for Qty1,Qty2 and Qty3, got only unique count. just want duplicate count as give example. like

region       store     qty1     qty 2     qty 3

delhi        1111      1          0          0

pune        1112       5          3          2

bang        1114     30        10          6

delhi        1115      14         10          10

pune        1116       5          3        

bang         1112     20        10          6

delhi        1111     1          0          0

pune        1115     51         33          32

bang         1114     20        10          6

Here no of lines  are 9  in which qty1 contains qty>0  but no of lines are 7 according to qty2 which contains qty2  >0 as well in qty3 are 6 lines. you can see above.

how can we solve it..?

sir , In my database, there are many duplicate lines are available like above, just want count all lines according to Qty1,Qty2,Qty3 in which in trio case qty1,qty2 and qty3 contain qty>0.

[Former Member]

But Still one question is pending..

How to count duplicate lines as

region       store     qty1     qty 2     qty 3

delhi        1111      1          0          0

pune        1112       5          3          2

bang        1114     30        10          6

delhi        1115      14         10          10

pune        1116       5          3         

bang         1112     20        10          6

delhi        1111     1          0          0

pune        1115     51         33          32

bang         1114     20        10          6

Here no of lines  are 9  in which qty1 contains qty>0  but no of lines are 7 according to qty2 which contains qty2  >0 as well in qty3 are 6 lines. you can see above.

how can we solve it..?

sir , In my database, there are many duplicate lines are available like above, just want count all lines according to Qty1,Qty2,Qty3 in which in trio case qty1,qty2 and qty3 contain qty>0.

Regards,

Shiv

[Loed]

Hi,

If QTY1, QTY2, and QTY3 are all formulas, just go to exception aggregation and choose COUNTER OF ALL DETAILED VALUES NOT EQUAL TO ZERO, reference characteristic to REGION..

If they are all SELECTIONS or RKF, then add three (3) formulas and insert QTY1 for FORMULA1, QTY2 for FORMULA2, and QTY3 for FORMULA3 then do the exception aggregation thing with reference to REGION..

Regards,

Loed

[Former Member]

Sir, When i am going to edit the formula and change the ref. characteristics, not able to edit in Variable window.pls help.

[Loed]

Hi,

Sorry did not get what you mean..

QTY1, QTY2, and QTY3 should be formulas to apply an exception aggregation..

What did you do so far? Post your query designer or post for new thread since this is a different topic already..

Regards,

Loed

[Former Member]

thank you  

[Former Member]

Sir,

Just want to show on  report layout no. of rows of every region.

region       store     qty1     qty 2     qty 3

delhi        1111      1          0          0

pune        1112       5          3          2

bang        1114     30        10          6

delhi        1115      14         10          10

pune        1116       5          3             0

bang         1112     20        10          6

delhi        1111     1          0          0

pune        1115     51         33          32

bang         1114     20        10          6

Here no of lines  are 9  in which qty1 contains qty>0  but no of lines are 7 according to qty2 which contains qty2  >0 as well in qty3 are 6 lines. you can see above.

OUtput should come like.

                              lines(rows)  in Qty1 qty2 and qty3

region          qty1>0               qty2 >0         qty3>0

Delhi               3                         1                    1              

pune               3                         3                    2

bang               3                         3                     3

so how can we get above result?

but according to your guidance got result like

region          qty1>0               qty2 >0         qty3>0

Delhi               1                        1                    1              

pune               1                         1                    1

bang               1                         1                     1

regards,

Shiv

[Loed]

Hi,

So you already have your QTY1, QTY2, and QTY3..

Create three (3) formulas, let's call them NEW_QTY1, NEW_QTY2, and NEW_QTY3..

For NEW_QTY1 insert QTY1..So,

NEW_QTY1 = QTY1

In the aggregation tab choose COUNTER OF ALL DETAILED VALUES NOT EQUAL TO ZERO, reference characteristic to REGION..

Do the same for NEW_QTY2 and NEW_QTY3..

Just post here for questions..

Regards,

Loed

[Former Member]

Hi Shiv,

Go with the Link provided by Loed.

I have one more link which you can refere too.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/009819ab-c96e-2910-bbb2-c85f7bdec...

Hope this helps too.

Thanks & Regards,

Maunank Patel.