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..
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..
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)..
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..
5.In your global variable, the formula should be (YOUR_FORMULA_VARIABLE x 1)..
SAVE your CKF..
6.Drag the Global CKF in the Key Figures structure of your query..
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:
P.S.
This is my first document so kinda excited when I made this..
Regards,
Loed
This is going to be a nice KT Loed...
Thanks for sharing!
Thank you mate, appreciate your comment! =)
Regards,
Loed
Thank you Loed.
It helped me and solved my problem.
Hi Radek,
You are very welcome.
Loed Despuig
Hi,
It helped me and solved my issue.
Thanks,
Santhanalakshmi
Good to know that. 🙂
Regards,
Loed
Hi Loed, thanks for your article. I managed to solve my problem.
Regards,
Tshediso
You are very welcome Tshediso Letseleha.
Thank you so much Loed. It helped me.