Skip to Content
Author's profile photo Loed Despuig

How to get unique count of values in one (1) or more characteristics

Hi guyz,

I noticed some posts having different scenarios but can be solve in same approach..With this, I thought of creating a document on how to count the number of unique values in one (1) or more characteristics in BEx Query Designer level..

In my scenario below, I need to count the unqiue number of customers who have transaction in a month..

Here are the steps:

1.Create a global CKF (let’s name it ZCKF_COUNT) – just right click the KEY FIGURES and choose NEW CALCULATED KEY FIGURE..

1.JPG

2.Create a formula variable (let’s name it ZCOUNT_CUSTID) for the object you wanted to count its unique values – just right click FORMULA VARIABLE and select NEW VARIABLE..

1.JPG

3.Type the DESCRIPTION and TECHNICAL NAME of your variable..

In the processing by option, choose REPLACEMENT PATH..

For reference characteristic, select your object (let’s say our object is ZCUST_ID)..

1.JPG

4.In the replacement path tab, choose INFOOBJECT in the Replace Variable With option..

Then in Replace With, choose ATTRIBUTE..

And in Attribute option, select CHARACTERISTIC REFERENCE (CONSTANT 1)..

SAVE your variable..

1.JPG

5.In your global variable, the formula should be (YOUR_FORMULA_VARIABLE x 1)..

SAVE your CKF..

1.JPG

6.Drag the Global CKF in the Key Figures structure of your query..

2.JPG

AND  YOU ARE ALREADY DONE..

If you wanted to count unique values based on two (2) or more characteristics, you need to do these steps first..

1.Create an infoobject that will be used to concatenate your infoobjects (let’s name it CONC_OBJ)

2.Insert CONC_OBJ to your infoprovider

3.Create a field routine for the CONC_OBJ so that your infoobjects will be concatenated, something like this:

CONCATENATE 1st_object 2nd_object into RESULT.

(just post here if you have problem in doing the field routine)

That’s it..

I hope you gain additional knowledge with this document..

You may check these posts for reference:

http://scn.sap.com/message/15286313#15286313       

http://scn.sap.com/message/15330495#15330495       

P.S.

This is my first document so kinda excited when I made this..

Regards,

Loed

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Aparajit Banik
      Aparajit Banik

      This is going to be a nice KT Loed...
      Thanks for sharing!

      Author's profile photo Loed Despuig
      Loed Despuig
      Blog Post Author

      Thank you mate, appreciate your comment! =)

      Regards,

      Loed

      Author's profile photo Radek Hosek
      Radek Hosek

      Thank you Loed.

      It helped me and solved my problem.

       

      Author's profile photo Loed Despuig
      Loed Despuig
      Blog Post Author

      Hi Radek,

      You are very welcome.

      Loed Despuig

       

      Author's profile photo santhana lakshmi k
      santhana lakshmi k

      Hi,

      It helped me and solved my issue.

      Thanks,

      Santhanalakshmi

      Author's profile photo Loed Despuig
      Loed Despuig
      Blog Post Author

      Good to know that. 🙂

      Regards,

      Loed

      Author's profile photo Tshediso Letseleha
      Tshediso Letseleha

      Hi Loed, thanks for your article. I managed to solve my problem.

      Regards,

      Tshediso

      Author's profile photo Loed Despuig
      Loed Despuig
      Blog Post Author

      You are very welcome Tshediso Letseleha.

      Author's profile photo Archana Ganti
      Archana Ganti

      Thank you so much Loed. It helped me.